Recreate VLookup in Access? (1 Viewer)

monvani · Jun 21, 2006

I need to recreate the excel function "VLookup" in Access. I've seen the function Dlookup in Access and (1) I can't figure out how to use it exactly * (2) I don't think it will do what I need it to do anyway.

Here's the situation:
I want the user to input a date into a field on a form. Based on the date, I want Access to reference another table I have that has date ranges and the corresponding accounting month (based on our work calendar).

Reference table looks like this:
Begin End AcctMonth
1/1/06 1/29/06 2006Jan
1/30/06 2/26/06 2006Feb
2/27/06 3/26/06 2006Mar

So, when a user inputs 1/31/06, I want access to return a value of "2006Feb".

I am an intermediate VBA user, but I don't even know what DAO is and I have never used an SQL. I've searched this topic on the internet and seen some examples but I haven't been able to decode what the SQL statement was saying.

Please help!

namliam · Jun 22, 2006

DLookup is what your looking for...

DLookup("[AcctMonth]","[YourTable]", "[begin] <= #" & format(userdate,"mm/dd/yyyy") & "#" and [end] <= #" & format(userdate,"mm/dd/yyyy") & "#"

This should do what you are looking for, tho it is not exactly like VLookup.

wazz · Jun 22, 2006

and a slightly different approach:

namliam · Jun 22, 2006

Which is? For the ones (like me) that are to lazy to look at your DB....

monvani · Jun 22, 2006

namliam:
When I paste that code into the expression builder for the text field on the form, i get the following error: "You have entered an operand without an operator". I changed the field and table names to the appropriate ones in my table. I assume that "userdate" in your expression is referring to the field name of the date input box on the form. Am I missing something?
Can you explain the purpose of the # signs and why they are necessary at the beginning and end of the expression?
"[begin] <= #" & format(userdate,"mm/dd/yyyy") & "#"
and
[end] <= #" & format(userdate,"mm/dd/yyyy") & "#"

I also included a close parenthesis at the end of the expression because it looked as though it is necessary to complete the dlookup function. Anyway, no matter what I tried, I continue to receive the exact same error message regarding the operand.

monvani · Jun 22, 2006

WAZZ:

For some reason, when I open your DB, the form automatically appears but none of the controls are present. When I open in design view, I see the text boxes and the command button, but when I switch to form view, it looks just like an empty form. Do you know why this might be happening? I'm using access 03 and XP.

namliam · Jun 23, 2006

I made a rather obvious error with the "s and with the )

Bad one:
DLookup("[AcctMonth]","[YourTable]", "[begin] <= #" & format(userdate,"mm/dd/yyyy") & "#" and [end] <= #" & format(userdate,"mm/dd/yyyy") & "#"
Right one:
DLookup("[AcctMonth]","[YourTable]", "[begin] <= #" & format(userdate,"mm/dd/yyyy") & "# and [end] <= #" & format(userdate,"mm/dd/yyyy") & "#")

That should work.

The #s... Well in the query it should look like this... #06/23/2006# making sure Access handles the value as (or converts it to) a date... The same way you put "Name" for strings...

wazz · Jun 23, 2006

namliam said:
Which is?

i set up a form based on a query of his table (i copied his data) with date criteria. the criteria are the same as yours. i put an unbound textbox and simple code to requery the form.

wazz · Jun 23, 2006

monvani said:
WAZZ: For some reason, when I open your DB, the form automatically appears but none of the controls are present. When I open in design view, I see the text boxes and the command button, but when I switch to form view, it looks just like an empty form. Do you know why this might be happening? I'm using access 03 and XP.

i'm afraid i have no idea why. you can either look at how everything works in design view and piece it together that way (Alt-F11 to see the simple code) or maybe creating a duplicate of the form by copying and pasting it will allow you to see everything.?.?.?

Recreate VLookup in Access? (1 Viewer)

monvani

Registered User.

namliam

The Mailman - AWF VIP

wazz

Super Moderator

Attachments

namliam

The Mailman - AWF VIP

monvani

Registered User.

monvani

Registered User.

namliam

The Mailman - AWF VIP

wazz

Super Moderator

wazz

Super Moderator

Similar threads

Users who are viewing this thread