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

Need_Excelhelp

New Member
Joined
Oct 14, 2014
Messages
22
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
 
I tried format around begDate as well with
DLookUp("[ccFees]","ccFee"," Format([begDate],"mm/dd/yyyy") =#" & Format([timestamp],"mm/d/yyyy") & "#")

But that gives me invalid syntax error. I am guessing it's because of the comma after begDate. Also, since I got a syntax error, I assumed you don't need a format function around begDate as changing it in properties would do the job.

Edit: just added ) after yyyy" and it still gives me syntax error.
 
Last edited:
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
I think the double-quotes around the "mm/dd/yyyy" are being confused with the literal double-quotes that surround that 3rd argument of the DLOOKUP function.

However, there is another way around this. It is important to understand how Access and Excel store dates. The store them as numbers, literally, the number of days since 1/1/1900. Any time component is stored as a fraction of a day. So, in order to get just the date portion, we just need to drop any decimal part it may have. We can do that using the INT function.

So this should work:
Code:
[COLOR=#333333]DLookUp("[ccFees]","ccFee","Int([begDate]) =#" & Int([timestamp]) & "#")[/COLOR]
 
Upvote 0
I think the double-quotes around the "mm/dd/yyyy" are being confused with the literal double-quotes that surround that 3rd argument of the DLOOKUP function.

However, there is another way around this. It is important to understand how Access and Excel store dates. The store them as numbers, literally, the number of days since 1/1/1900. Any time component is stored as a fraction of a day. So, in order to get just the date portion, we just need to drop any decimal part it may have. We can do that using the INT function.

So this should work:
Code:
[COLOR=#333333]DLookUp("[ccFees]","ccFee","Int([begDate]) =#" & Int([timestamp]) & "#")[/COLOR]

Unfortunately, it doesn't. I am still getting blank cells under this field. I am so sorry for this hassle.
 
Upvote 0
I would recommend creating some temporary queries just to check to verify that it is returning the information you think.
In this query where you are trying to add this calculation, enter this calculated field and see what it returns:
Code:
Check1: Int([timestamp])
Then, create a new temp query based on your ccFee table and enter this calculated field:
Code:
Check2: Int([begDate])

Now, compare the two. In looking at "Check1", can you find and exact match for it in "Check2"?
 
Upvote 0
I would recommend creating some temporary queries just to check to verify that it is returning the information you think.
In this query where you are trying to add this calculation, enter this calculated field and see what it returns:
Code:
Check1: Int([timestamp])
Then, create a new temp query based on your ccFee table and enter this calculated field:
Code:
Check2: Int([begDate])

Now, compare the two. In looking at "Check1", can you find and exact match for it in "Check2"?

OMG this is where I was wrong. None of these are exact match. Okay, let me see if I can illustrate a sample of my ccFee table.
begDateccFees
01/01/20141%
02/01/20142%
03/01/20143%
04/01/20142%
05/01/20141%

<tbody>
</tbody>

Now my timeStamp has all the transactions with the "time" that it occurred. I want access to calculate ccFees based on the month. So if anything that falls between Jan and Feb is 1%. So basically, I want it to look at my timestamp and find the right ccFee between two dates. This is how I did it in VLook up, but I guess it doesn't work the same way in Access.
 
Upvote 0
OK. So it appears that we want just the Month and Year to compare. Here is how I would do this:

Create a query based on your "ccFee" table, and add the "ccFees" and "begDate" field to it.
Now, create a calculated field in that query like this:
Code:
YMbegDate: Format([begDate],"yyyymm")
Let's name this query "ccFeeQuery".

Now update your DLOOKUP calculation in your other query to use this new calculated field in this intermediate query we just created, i.e.
Code:
Dlookup("[ccFees]","ccFeeQuery","[YMbegDate]=" & Format([timestamp],"yyyymm"))
 
Upvote 0
OK. So it appears that we want just the Month and Year to compare. Here is how I would do this:

Create a query based on your "ccFee" table, and add the "ccFees" and "begDate" field to it.
Now, create a calculated field in that query like this:
Code:
YMbegDate: Format([begDate],"yyyymm")
Let's name this query "ccFeeQuery".

Now update your DLOOKUP calculation in your other query to use this new calculated field in this intermediate query we just created, i.e.
Code:
Dlookup("[ccFees]","ccFeeQuery","[YMbegDate]=" & Format([timestamp],"yyyymm"))

Finally, it works!!! Joe, you are a genius! Thank you so much. And I am really sorry, had I been more clear about what I exactly wanted in the beginning, all this could have been avoided.

Happy Holidays!

Best regard,

N_EH
 
Upvote 0
Glad you got it worked out, and hoped you learned a few new "tricks" along the way.
 
Upvote 0
Glad you got it worked out, and hoped you learned a few new "tricks" along the way.

I did. Used some of the "formatting" knowledge to fix the errors I was getting on my other queries. I had changed formats using properties and assumed it to be the same.

But I have a feeling I may come back here soon. Working on SUMIFFs version of Access and I feel like I may need help. But before that, I want to see how far I can go without any help.

Thank you for all your prompt replies with patience.
 
Upvote 0
Working on SUMIFFs version of Access and I feel like I may need help. But before that, I want to see how far I can go without any help.
Hint: Take a look at "Aggregate (Totals) Queries".

If you run into questions, just be sure to post a new thread (since it is a new topic).

Have a good weekend!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top