DLookup Criteria Syntax

[Lucky33]

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]

See if this helps, you're not treating the string value as such:

General: DLookup Usage Samples

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 are trying to bite off too much at once. Break it into smaller pieces. Something like

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]

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

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

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

 

[arnelgp]

you can also test this:

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]

You are trying to bite off too much at once. Break it into smaller pieces. Something like

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]

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]

Maybe something like

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]

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]

The line
strShare = "'" & strShare "'"
is giving compile error: expected : end of statement

 

[arnelgp]

The latest date in that query is 4/23/2021

is it from The Same Symbol?

 

[MajP]

strShare = "'" & strShare &"'"
missing a &

 

[Lucky33]

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]

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]

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.

 

[Lucky33]

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

 

[arnelgp]

here is the demo. press the button to get the Symbol and DValue.

 

[Lucky33]

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

 

[Lucky33]

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

 

[Lucky33]

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