Dlookup Variable

[CharlieT302]

Hello Everyone,

I used the following code below to avoid having to write out a complete Dlookup formula when I need to reference it. However, the SD code, which produces a date within a series of records, will not display anything at all. As a test, I used the complete formula in conjunction with a Msgbox and it worked fine. Using the abbreviated SD, however, fails. Below is sample code that is having the error. I am assuming the issue is with Dim. I have tried different variations (Dim SDC, Dim SDC As Date, etc.)

Ideas?

Dim SDC
SD = DLookup("Serve_Date", "tbl_Meal_Orders_Site", "menu_ID = " & Me.Order_Menu_ID & " And Offer_vs_Serve = -1 " & " And Not IsNull([Serve_Date])")

'this code fails
If Me.Meal_Qty > 0 And (IsNull(Me.Serve_Date) Or Me.Serve_Date = "") Then
'Me.Serve_Date = SD


'this code works
If Me.Meal_Qty > 0 And (IsNull(Me.Serve_Date) Or Me.Serve_Date = "") Then
Me.Serve_Date = DLookup("Serve_Date", "tbl_Meal_Orders_Site", "menu_ID = " & Me.Order_Menu_ID & " And Offer_vs_Serve = -1 " & " And Not IsNull([Serve_Date])")

 

[Andrzejek]

Do you have Option Explicit on?
I ask because you declare variable SDC, but you use SD
Unless this is just a typo...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[CharlieT302]

Oops. Sorry. Yes, that was a typo.

It should read:

Dim SD
SD = DLookup("Serve_Date", "tbl_Meal_Orders_Site", "menu_ID = " & Me.Order_Menu_ID & " And Offer_vs_Serve = -1 " & " And Not IsNull([Serve_Date])")

'this code fails
If Me.Meal_Qty > 0 And (IsNull(Me.Serve_Date) Or Me.Serve_Date = "") Then
'Me.Serve_Date = SD

 

[Andrzejek]

What do you get from the blue lines of code:

[green]
'this code fails[/green]
[blue]
Debug.Print "Me.Meal_Qty is " & Me.Meal_Qty 
Debug.Print "Me.Serve_Date is " & Me.Serve_Date
[/blue]
If Me.Meal_Qty > 0 And (IsNull(Me.Serve_Date) Or Me.Serve_Date = "") Then
Me.Serve_Date = SD

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[CharlieT302]

Thanks for your input. Just to streamline this a bit, let me ask the question differently.

The Dlookup formula is fine. All I really want is a way to reference it without retyping it over and over.

For example:
If Me.Serve_Date = SD then
<take some action>

I would like SD to represent the Dlookup formula. How can I do that?

 

[Andrzejek]

I would guess you want to create a Function that will do the DLookup, name is SD and use it in many places.

How about:

Private Function [blue]SD[/blue]() [green]'As Date?  As String?[/green]

If Me.Meal_Qty > 0 And (IsNull(Me.Serve_Date) Or Me.Serve_Date = "") Then
    [blue]SD[/blue] = DLookup("Serve_Date", _
    "tbl_Meal_Orders_Site", "menu_ID = " & Me.Order_Menu_ID & _
    " And Offer_vs_Serve = -1 " & " And Not IsNull([Serve_Date])")
End If

End Function

Then you can use it as you want:

If Me.Serve_Date = [blue]SD[/blue] then[green]
<take some action>[/green]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Beilstwh]

I'm also curious what are you defining SD as, you are not defining it at all. Shouldn't you be using

Dim SD as date

Bill
Lead Application Developer
New York State, USA