Reading a date field

[bscs1963]

I am coding in vba looking where i am filtering using the following lines.

set rsTemp1 currentdb.openrecordset ("tblReceiving")
set rstemp2 = currentdb.openrecordset ("Select * From [Table1] Where Date = rstemp1!ReceiptDate")

I know that if the ReceiptDate is a text field, then the the entry would be Where Date = '" & rstemp1!ReceiptDate & "'

What is the proper syntax so it realizes that it is a date field?

Can someone also tell me what the proper syntax is for a numeric field?

This seems to be something that is confusing me.

Thanks

I keep getting mismatch in the filter.

 

[dhookom]

You delimited dates with # like:

set rsTemp1 currentdb.openrecordset ("tblReceiving")
set rstemp2 = currentdb.openrecordset ("Select * From [Table1] Where Date =#" & rstemp1!ReceiptDate & "#")

Numeric values require no delimiter.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

I would even go with something like:

[blue]Dim strSQL As String[/blue]
set rsTemp1 currentdb.openrecordset ("tblReceiving")[blue]
strSQL = "Select * From [Table1] Where Date =#" & rstemp1!ReceiptDate & "#"
Debug.Print strSQL[/blue]   [green]'See what your DB sees[/green]
set rstemp2 = currentdb.openrecordset ([blue]strSQL[/blue])

This is nice not only because you can see your SQL, but also if you decide to use some error handler and write into an error log, you can write your strSQL into error log.

Have fun.

---- Andy

 

[MajP]

I use these especially if working with non US settings.

Public Function sqlTxt(varItem As Variant) As Variant
  'for string values in sql
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function

Function SQLDate(varDate As Variant) As Variant
    'For date values in sql 
    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

And always do what Andy says about first saving it to a string.

strSQL = "Select * From [Table1] Where Date = " & sqlDate(rstemp1!ReceiptDate)
Debug.Print strSQL   'See what your DB sees

 

[Andrzejek]

Or - if you do not want to use [tt]Debug.Print[/tt] - you can always put break on the line

set rstemp2 = currentdb.openrecordset (strSQL)

and in your Immediate Window type:
[tt]
? strSQL[/tt]

Have fun.

---- Andy