DLookup

  • Thread starter Thread starter Kerry
  • Start date Start date
K

Kerry

Hi

Please could someone help me work out why this isn't
working (copy of code below). I hope/think it is just
something silly but have tried and tried. This procedure
will work fine if I use just one set of criteria, it
doesn't like 2. I found some info through google which
suggested I put my criteria into variables first when
using multi criteria but now I am getting error "Data
type mismatch in criteria expression. (Error 3464)"

Many Thanks

Kerry
-----------------
Private Sub To_AfterUpdate()
DoCmd.Save

siteF = Me.From
siteT = Me.To

RecMiles = DLookup("[Mileage (Miles)]", "Qry Miles & Dur
Calc", "[Start] = '" & siteF & "' And [End] = " & siteT)

'RecDur = DLookup("[Duration (Mins)]", "Qry Miles & Dur
Calc", "[start] = " _
& Me.From)
'And "[end] = " & Me.To)

Me.Mileage = RecMiles
Me.Duration = RecDur
End Sub
 
Hi

Please could someone help me work out why this isn't
working (copy of code below). I hope/think it is just
something silly but have tried and tried. This procedure
will work fine if I use just one set of criteria, it
doesn't like 2. I found some info through google which
suggested I put my criteria into variables first when
using multi criteria but now I am getting error "Data
type mismatch in criteria expression. (Error 3464)"

Many Thanks

Kerry
-----------------
Private Sub To_AfterUpdate()
DoCmd.Save

siteF = Me.From
siteT = Me.To

RecMiles = DLookup("[Mileage (Miles)]", "Qry Miles & Dur
Calc", "[Start] = '" & siteF & "' And [End] = " & siteT)

'RecDur = DLookup("[Duration (Mins)]", "Qry Miles & Dur
Calc", "[start] = " _
& Me.From)
'And "[end] = " & Me.To)

Me.Mileage = RecMiles
Me.Duration = RecDur
End Sub
Click to expand...

as the error tells you: DataType mismach

right of the equal sign has to be the same datatype as left

if it's a string (left) then it has to be quotet (right)

"[Start] = '" & siteF & "" if this is working then start is a string
and siteF is a string

asuming that your [end] is also a string then you forgot the quotes

BTW: your field and table names will give you a lot of troubles, try
to avoid everything what's not a letter or number or write all in [ ],
also your table or query names
 
RecMiles = DLookup("[Mileage (Miles)]", "Qry Miles & Dur
Calc", "[Start] = '" & siteF & "' And [End] = " & siteT)

'RecDur = DLookup("[Duration (Mins)]", "Qry Miles & Dur
Calc", "[start] = " _
& Me.From)
'And "[end] = " & Me.To)
Click to expand...

There are a number of problems here:

The query name contains illegal characters, and therefore needs to be
enclosed in braces: [Qry Miles & Dur Calc]

As Andi points out, you don't mention the data types of !From and !To, and
you haven't dimmed the variables siteF and siteT, but there is rich scope
there for them needing to be properly formatted and quoted before being
passed to the criterion string.

From is a reserved word in SQL and To is reserved in VBA, so using these
for column names is a Really Bad Idea (although not actually illegal). You
also seem to be switching between From & To and Start & End -- you will
find life much easier if you stabilise your own vocabulary

Doing two database fetches for these is really unkind to anyone who wants
to share the network: try doing them both at once...

Dim rst As DAO.Recordset
Dim strSQL As String

'
' make string carefully. Note the formatting function: this
' is up to you according to what is actually in the
' things
'
strSQL = "SELECT [Mileage (Miles)], [Duration (Mins)] " & vbCrLf & _
"FROM [Qry Miles & Dur Calc] " & vbCrLf & _
"WHERE [Start] = " & Formatted(Me.From) & " " & vbCrLf & _
" AND [End] = " & Formatted(Me.To) & ";"

' check it out
MsgBox strSQL

' and get it
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)
If Not rst.BOF Then
' return the results
RecMiles = rst![Mileage (Miles)]
RecDur = rst![Duration (Mins)]

Else
MsgBox "Cannot find any record",vbCritical

End If

rst.Close


.... which looks like a bit more work but is actually much less.

All the best


Tim F
 
Back
Top