Thanks Bill. My day job is getting in the way 
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: wrmosca@comcast.net
Date: Fri, 2 May 2014 11:32:51 -0700
Subject: RE: [MS_AccessPros] Dlookup in (sub)Report
Sarah
---In MS_Access_Professionals@yahoogroups.com, <sarahk@schemesoftware.com> wrote :
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: wrmosca@comcast.net
Date: Fri, 2 May 2014 11:32:51 -0700
Subject: RE: [MS_AccessPros] Dlookup in (sub)Report
Sarah
It looks like Duane is offline so I'll jump in.
When you use a variable that is going into a string for a query (which is what a domain aggregate is like DLookup, DMax, etc) You can't just plop it into the string. Access's database engine can't read the value. You have to use the right qualifiers: single/double quotes for strings, hash marks for datetime AND concatenate the variable/control value, not the variable/control itself.
On the other hand, when you are working just within VBA code Access knows the value within that context so there is no need for qualifiers.
Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com
---In MS_Access_Professionals@yahoogroups.com, <sarahk@schemesoftware.com> wrote :
Duane,
This information is extremely useful and I will keep it close to my heart.
What I dont understand is that in VB code, sometimes a=b works and sometimes a=""" & [b] & """") is neccessesary. When does that apply?.
Sorry, about the thread, I did not realize that I need to click 'show message history', I thought it was the default.
Thanks again for your help
Sarah
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Sarah,
It typically helps us older/mature members if you include enough of the previous messages to at least jog our failing memory.
If Numeric:
=DLookUp("FieldName","TableName","[TextField] = '" & [StringValue] & "'")
If text/string that might contain an apostrophe like O'Brien:
=DLookUp("FieldName","TableName","[TextField] = """ & [StringValue] & """")
If Date:
=DLookUp("FieldName","TableName","[DateField] = #" & [DateValue] & "#")
You may need to copy and paste the above into Word or some place that you can enlarge and change the font to fixed space.
It typically helps us older/mature members if you include enough of the previous messages to at least jog our failing memory.
If Numeric:
=DLookUp("FieldName","TableName","[NumericField]= " & [NumberValue])
If text/string that will never contain an apostrophe like O'Brien:=DLookUp("FieldName","TableName","[TextField] = '" & [StringValue] & "'")
If text/string that might contain an apostrophe like O'Brien:
=DLookUp("FieldName","TableName","[TextField] = """ & [StringValue] & """")
If Date:
=DLookUp("FieldName","TableName","[DateField] = #" & [DateValue] & "#")
You may need to copy and paste the above into Word or some place that you can enlarge and change the font to fixed space.
To: MS_Access_Professionals@yahoogroups.com
From: sarahk@...
Date: Thu, 1 May 2014 08:52:11 -0700
Subject: RE: [MS_AccessPros] Dlookup in (sub)Report
Duane, Thank you!! Your syntax made it work. I am totally confused on how to use the &,",# etc symbols. I dont know when and how to use it and find it very confusing.
Thanks again.
Sarah
From: sarahk@...
Date: Thu, 1 May 2014 08:52:11 -0700
Subject: RE: [MS_AccessPros] Dlookup in (sub)Report
Duane, Thank you!! Your syntax made it work. I am totally confused on how to use the &,",# etc symbols. I dont know when and how to use it and find it very confusing.
Thanks again.
Sarah
__._,_.___
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
Did you check out automatic photo albums in Yahoo Groups?
When you send mail to your Yahoo Group with photos attached, a photo album with attached photos is automatically created. When your group members reply to the email with photo attachments, those photos are added to the 'photomatic' albums automatically!
.
__,_._,___
Tidak ada komentar:
Posting Komentar