DLookUp with one text criteria and one date criteria - syntax error

[mcmad]

I would appreciate help with the following:



I am trying to perform a DLookUp using a text value from a field in a table that is used in my form along with a date field stored in a table and also used in my form. The idea is to lookup a particular price at a particular date in my Shares table but I am at wits end trying to get the syntax correct. I was able to successfully perform a DLookUp from a Test table using a text field on its own and a date field on its own, but the multiple criteria has me lost. The following is my attempt.

Private Sub ClosePrice_Click()


Dim VarX As String
If IsNull(VarX = DLookup("Close", "Shares", "[ASX Code] = " & Forms!WatchList_Notes!ASX _
& " AND [CloseDate] = #" & Format(Forms!WatchList_Notes!NoteDate, "yyyy-mm-dd") & "#")) Then


MsgBox "No Value Found"
MsgBox Forms![WatchList_Notes]![ASX]
MsgBox Format(Forms![WatchList_Notes]![NoteDate], "yyyy-mm-dd")
Else


VarX = DLookup("Close", "Shares", "[ASX Code] = " & Forms!WatchList_Notes!ASX _
& " AND Format(CloseDate, ""yyyy-mm-dd") = "#" & Format(Forms!WatchList_Notes!NoteDate, "yyyy-mm-dd") & "#)"


Forms![WatchList_Notes].[ClosePrice] = Price


End If


End Sub

The following is the error message:



I will appreciate any help on the subject.

Thanks, David

[June7]

Format returns a string value, not a true date.
And you have some concatenation errors.

Try:

VarX = DLookup("Close", "Shares", "[ASX Code] = " & Forms!WatchList_Notes!ASX _
& " AND Format(CloseDate, 'yyyy-mm-dd') = '" & Format(Forms!WatchList_Notes!NoteDate, "yyyy-mm-dd") & "'")

[jojowhite]

Private Sub ClosePrice_Click()


Dim VarX As String
VarX = DLookup("Close", "Shares", "[ASX Code] = " & Forms!WatchList_Notes!ASX _
& " AND [CloseDate] = #" & Format(Forms!WatchList_Notes!NoteDate, "yyyy-mm-dd") & "#")) 

If IsNull(VarX) Then


MsgBox "No Value Found"
MsgBox Forms![WatchList_Notes]![ASX]
MsgBox Format(Forms![WatchList_Notes]![NoteDate], "yyyy-mm-dd")
Else



Forms![WatchList_Notes].[ClosePrice] = VarX


End If


End Sub

[mcmad]

Format returns a string value, not a true date.
And you have some concatenation errors.

Try:

VarX = DLookup("Close", "Shares", "[ASX Code] = " & Forms!WatchList_Notes!ASX _
& " AND Format(CloseDate, 'yyyy-mm-dd') = '" & Format(Forms!WatchList_Notes!NoteDate, "yyyy-mm-dd") & "'")

Many thanks for your help, I do however now get another error, see below:

[mcmad]

Private Sub ClosePrice_Click()


Dim VarX As String
VarX = DLookup("Close", "Shares", "[ASX Code] = " & Forms!WatchList_Notes!ASX _
& " AND [CloseDate] = #" & Format(Forms!WatchList_Notes!NoteDate, "yyyy-mm-dd") & "#")) 

If IsNull(VarX) Then


MsgBox "No Value Found"
MsgBox Forms![WatchList_Notes]![ASX]
MsgBox Format(Forms![WatchList_Notes]![NoteDate], "yyyy-mm-dd")
Else



Forms![WatchList_Notes].[ClosePrice] = VarX


End If


End Sub

Thank you for helping, I still get an error as below:


On other records I get the following:


I have made certain the records are in both tables with the correct dates.

[June7]

[ASX Code] has text value so need apostrophe delimiters.

VarX = DLookup("Close", "Shares", "[ASX Code] = '" & Forms!WatchList_Notes!ASX _
& "' AND Format(CloseDate, 'yyyy-mm-dd') = '" & Format(Forms!WatchList_Notes!NoteDate, "yyyy-mm-dd") & "'")

[mcmad]

I cant thank you enough, problem solved and works perfectly. This is my first go at using MS-Access and I must admit I have been trying for days to get this working.

Thank you again.

[mcmad]

Thank you for your help.

[mcmad]

I cant thank you enough, problem solved and works perfectly. This is my first go at using MS-Access and I must admit I have been trying for days to get this working.

Thank you again.

[mcmad]

[ASX Code] has text value so need apostrophe delimiters.

VarX = DLookup("Close", "Shares", "[ASX Code] = '" & Forms!WatchList_Notes!ASX _
& "' AND Format(CloseDate, 'yyyy-mm-dd') = '" & Format(Forms!WatchList_Notes!NoteDate, "yyyy-mm-dd") & "'")

Sorry I keep posting with a reply instead of quote (I have lost the plot today )