DLookup Criteria Syntax (1 Viewer)

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
Hi everyone. I need help in the syntax of the DLookup() and I appreciate your efforts

I am trying to get a value from a query to place in a form using the DLookup function. This value must meet two criteria: must be on the last date, and, it is the one for the text field that match the specific text field on the form from which this procedure is initiated. Please note the following:

  • The underlying table of the query, and the underlying table of the form are not related
  • The string field from the query must match a “part” of the string field in the form


I am stumped with the syntax of the criteria for matching the string from the query with that of the form. Here is part of the code that is causing the error:

Dim doShare as Double

Dim stShare as String

stShare = me.Desc ‘Desc is the text field in the form that contains the string to match

doShare = DLookup("[QueStocksDaily]![TotValue] ", "QueStocksDaily", _

" [QueStocksDaily]![DDate] =" & "#" & DMax(" [QueStocksDaily]![DDate] ", "QueStocksDaily") & "#" AND _

"*[QueStocksDaily]![Symbol]* like " & stShare)



The date part is working fine. The last one is the issue, at least for me. I am showing here the simplest form, so you can see what I am trying to do, as I tried many variations of using the single quotes and double quotes that I could think of, without success.

Once again, the field on the form, contains (not equal to) the field in the query. Here is a sample data that may help too:

Desc = Microsoft (MSFT)

Symbol = MSFT

Thank you again for all and any effort :)
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:02
Joined
Aug 30, 2003
Messages
36,118
See if this helps, you're not treating the string value as such:


You also have extraneous quotes around the AND, and I don't know what you're trying to do with asterisks around the field name (they'd go around the value, not the field name). I'd try to get it working on one line before adding line breaks; I suspect that's confusing the issue for you.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:02
Joined
May 21, 2018
Messages
8,463
You are trying to bite off too much at once. Break it into smaller pieces. Something like
Code:
dim stShare as String
dim Criteria as string
dim maxDate as date
dim strMaxDate as string

stShare = me.Desc
strShare = "'*" & strShare "*'"
maxDate =  DMax("[DDate]", "QueStocksDaily")
strMaxDate = SqlDate(MaxDate)
Criteria = "DDate = " & strMaxDate & " AND Symbol like " & strShare
debug.print criteria
doShare = DLookup("TotValue", "QueStocksDaily",Criteria)

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
 

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
Thank you pbaldy
let me first clear the use of the asterisks which are wildcards. In my example Desc=Microsoft (MSFT) and does not match Symbol=MSFT, but *MSFT* = Microsoft (MSFT) .

Thank you for the link. But the examples are so basic and none tackles the issue I am facing.
You are trying to bite off too much at once. Break it into smaller pieces. Something like
Code:
dim stShare as String
dim Criteria as string
dim maxDate as date
dim strMaxDate as string

stShare = me.Desc
strShare = "'*" & strShare "*'"
maxDate =  DMax("[DDate]", "QueStocksDaily")
strMaxDate = SqlDate(MaxDate)
Criteria = "DDate = " & strMaxDate & " AND Symbol like " & strShare
debug.print criteria
doShare = DLookup("TotValue", "QueStocksDaily",Criteria)

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
You are trying to bite off too much at once. Break it into smaller pieces. Something like
Code:
dim stShare as String
dim Criteria as string
dim maxDate as date
dim strMaxDate as string

stShare = me.Desc
strShare = "'*" & strShare "*'"
maxDate =  DMax("[DDate]", "QueStocksDaily")
strMaxDate = SqlDate(MaxDate)
Criteria = "DDate = " & strMaxDate & " AND Symbol like " & strShare
debug.print criteria
doShare = DLookup("TotValue", "QueStocksDaily",Criteria)

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:02
Joined
May 7, 2009
Messages
19,169
you can also test this:
Code:
Dim doShare As Double
Dim stShare As String
Dim vValue As Variant
Dim sDate As String
Dim sSymbol As String
stShare = Me.Desc 'Desc is the text field in the form that contains the string to match
vValue = DMax("Symbol & '|' & DDate", "QueStocksDaily", "'" & stShare & "' Like '*' & [Symbol] & '*'") & ""
If Len(vValue) <> 0 Then
    sSymbol = Split(vValue, "|")(0)
    sDate = Format(Split(vValue, "|")(1), "\#mm\/dd\/yyyy\#")
    'Debug.Print sSymbol, sDate

    doShare = DLookup("[TotValue]", "QueStocksDaily", _
                    "[Symbol] = '" & sSymbol & "' And [DDate] =" & sDate)
End If
 

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
You are trying to bite off too much at once. Break it into smaller pieces. Something like
Code:
dim stShare as String
dim Criteria as string
dim maxDate as date
dim strMaxDate as string

stShare = me.Desc
strShare = "'*" & strShare "*'"
maxDate =  DMax("[DDate]", "QueStocksDaily")
strMaxDate = SqlDate(MaxDate)
Criteria = "DDate = " & strMaxDate & " AND Symbol like " & strShare
debug.print criteria
doShare = DLookup("TotValue", "QueStocksDaily",Criteria)

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function
Thank you MajP for what will probably work, but I need the wildcard around Symbol and not around Desc (Desc contains Symbol not the other way around)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:02
Joined
May 21, 2018
Messages
8,463
I do not understand @arnelgp's code, if that works it is something I have never seen. I can not wrap my head around how this can be done in SQL
"'" & stShare & "' Like '*' & [Symbol] & '*'"
I have never seen a field on the right side of a like.

As far as I know doing a wildcard around the field is not possible.
I would have done an instr instead
Criteria = "DDate = " & strMaxDate & " AND instr( " & strShare & "," & [symbol]& ")" > 0

However, I think I would have rolled my own recordset and this would be much easier.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:02
Joined
May 21, 2018
Messages
8,463
Maybe something like
Code:
dim stShare as String
dim rs as dao.recordset
Dim strSql

stShare = me.Desc
strShare = "'" & strShare "'"
dim strSql as string

strSql = "Select top 1 TotValue from QueStocksDaily where instr(" & strShare & ", [Symbol]) > 0 Order by DDate Desc")
debug.print strSql
currentdb.openrecordset(strSql)
doShare = RS!TotValue

The instr will find where a Field value is part of another string.
 

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
Well, the code of arnelgp went through, but worked partially true. There were no errors, and I got totals from the query matching the Desc, but not from the latest date as intended. The latest date in that query is 4/23/2021, and all results were from 9/9/2020
So it looks like the date part needs some tweaking. What say you arnelgp? :) :)
Meanwhile I will try the code of MajP and see where it leads
thank you
 

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
The line
strShare = "'" & strShare "'"
is giving compile error: expected : end of statement
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:02
Joined
May 21, 2018
Messages
8,463
strShare = "'" & strShare &"'"
missing a &
 

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
is it from The Same Symbol?
The query is a list of the same number of stocks, and their daily value. Those values are entered daily. And the purpose of what I am doing is to get the latest value for each stock (symbol) to be used in another table
your code is returning the corresponding value for each symbol, but for the date 9/9/2020 instead the last one that is 4/23/2021.
 

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
strShare = "'" & strShare &"'"
missing a &
thanks MajP. that took care of this error, but now there is another one that popped out.
the line strSql = "Select top 1 TotVal.....
is giving again compile error: expected: end of statement ... and highlighting the last parenthesis
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:02
Joined
May 7, 2009
Messages
19,169
I am Unable to replicate your situation.
i am testing it on my db and is Correctly returning the Max Date.
Unless your DDate is a String and not a Real date.

EDIT:

can you show a snapshot of Datasheet view of QueStocksDaily that shows the DDate and Symbol fields.
 
Last edited:

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
I am Unable to replicate your situation.
i am testing it on my db and is Correctly returning the Max Date.
Unless your DDate is a String and not a Real date.

EDIT:

can you show a snapshot of Datasheet view of QueStocksDaily that shows the DDate and Symbol fields.
DDate is a real date, no doubt there. Am attaching a snapshot of sample data, the real one has exactly the same structure, but much more symbols.
Tell me, would the ID number for each record be of better use in your code instead of the date. i noticed that you are getting DMax values for both DDate and Symbol in one expression. The id number DayID that is an auto number for each record in the underlying table to the query
 

Attachments

  • QueStocksDaily.jpg
    QueStocksDaily.jpg
    380.8 KB · Views: 437

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:02
Joined
May 7, 2009
Messages
19,169
here is the demo. press the button to get the Symbol and DValue.
 

Attachments

  • demo.accdb
    672 KB · Views: 471

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
here is the demo. press the button to get the Symbol and DValue.
this is really turning my head.. while at your end you are getting the right results, here is a sample when i run the same at my end
 

Attachments

  • Screenshot 2021-04-27 211646.jpg
    Screenshot 2021-04-27 211646.jpg
    41.8 KB · Views: 266

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
this is really turning my head.. while at your end you are getting the right results, here is a sample when i run the same at my end
and here is the underlying table of the query QueStocksDaily. I went through all the dates in the table to see if there was any deviation after 9/9/2021 but everything looks normal
am really sorry for all the trouble
 

Attachments

  • TableOfDailyStocks.jpg
    TableOfDailyStocks.jpg
    110 KB · Views: 420

Lucky33

Registered User.
Local time
Today, 15:02
Joined
Sep 3, 2011
Messages
57
here is the demo. press the button to get the Symbol and DValue.
FYI.. I replaced in your code all references to the date and DDate with the DayID, the key for each record, and i got similar results, only this time TotValue dated 2/4/2021 .... go figure
 

Users who are viewing this thread

Top Bottom