DLookup syntax error- can't figure out what I am doing wrong.

[Need_Excelhelp]

Hi,

I am a complete newbie at Access. Through google, I learned that DLookup is the access version of Vlookup.

I tried the following function in various ways and am getting syntax- (missing operator) in query expression '[begDate]=9/28/...' error.

1. net: DLookUp("[ccFee]","ccFee","[begDate] =" & [timestamp])
2. net: DLookUp("[ccFee]","ccFee","[begDate] ='" & [timestamp]&"'")
3. net: DLookUp("[ccFee]","ccFee","[begDate] =" & Chr(34) & [timestamp] & Chr(34))

In the first option, I am doing exactly the same as this YT tutorial.

However, I have no idea what I am doing wrong and why I am getting the same errors. I also tried by adding and deleting spaces between characters.

Is there any other way to do a "Vlookup" in Access?

I would appreciate all your help. Thank you so much in advance.

Regards,

N_EH

 

[Joe4]

Why are your first and second arguments the same value ("ccFee")?
The first argument is the name of the FIELD you want to return.
The second argument is the name of the TABLE/QUERY that field is found in.
I think you want the third argument to look something like this:

[COLOR=#333333]"[begDate] =#" & [timestamp] & "#"[/COLOR]

See here for more help on DLOOKUP: MS Access: DLookup Function

 

[Need_Excelhelp]

Thank you for your prompt reply.

I have a table that's called ccFee and in that table, there are two fields, begDate and ccFee. What I want to do is look at the timestamp in my current query, and match it with the begDate in ccFee Table to get the correct ccFee attributed to this field.

I will try your suggestion in argument 3 and look at the link you provided and let you know how it goes.

Once again, thank you so much for your help!

 

[Joe4]

I would also recommend that you try to avoid having field names the exact same name as table names. It could cause confusion.

 

[Need_Excelhelp]

Why are your first and second arguments the same value ("ccFee")?
The first argument is the name of the FIELD you want to return.
The second argument is the name of the TABLE/QUERY that field is found in.
I think you want the third argument to look something like this:

[COLOR=#333333]"[begDate] =#" & [timestamp] & "#"[/COLOR]

See here for more help on DLOOKUP: MS Access: DLookup Function

So, I was using the string format instead of date. It seems to have removed the error, but now when I run the query, everything is blank in net column.

Edit: Just changed the ccFee field to ccFees--and made appropriate change in the current query. Do you know why might the cells in net column be blank?

 

[Joe4]

Are your begdate and timestamp fields both Date fields and not Text fields?

You might need to do something like:

[COLOR=#333333]"[begDate] =#" & Format([timestamp],"mm/dd/yyyy") & "#"[/COLOR]

 

[Need_Excelhelp]

Are your begdate and timestamp fields both Date fields and not Text fields?

You might need to do something like:

[COLOR=#333333]"[begDate] =#" & Format([timestamp],"mm/dd/yyyy") & "#"[/COLOR]

No, they both are in the same "mm/dd/yyyy" format.

 

[Joe4]

If they are both in date format, the structure like in post #2 should work.
It is difficult to know exactly what the issue may be without setting your data tables.
Are you sure that neither date field has a time piece to it?

 

[Need_Excelhelp]

If they are both in date format, the structure like in post #2 should work.
It is difficult to know exactly what the issue may be without setting your data tables.
Are you sure that neither date field has a time piece to it?

Originally, they both have time attached to it. I have formated the begDate field in ccFee table to mm/dd/yyyy and have added format() of mm/dd/yyyy around timestamp like you mentioned in the previous reply.

This is the expression I have right now:
DLookUp("[ccFees]","ccFee","[begDate]=#" & Format([timestamp],"mm/dd/yyyy") & "#")

p.s. Is there any way I can send the two tables on this website so you could have a look?

 

[Joe4]

Changing the FORMAT property on the field itself does not remove/eliminate the time component of that field. It just "hides" it from view. But it is still there, so the values will not be equal. The FORMAT function converts the value to Text, so then it would work, but you would have to apply it to both fields so you get a Text-to-Text comparison.