Using dates with VBA, Access and SQL

[max]

Hi,



Please help me debug this code.

Variable 'TestDte' is defined as 'Date' data type. It is taken from a form that is populated from a table where dates are also stored as 'Date' data type. When I hover over TestDte in break mode I see a date in the format dd\mm\yyyy

I continuously get a data type mismatch error when I run this query, I get the same error if 'TestDte' remains in format dd\mm\yyyy

Set Rst4 = CurrentDb.OpenRecordset("SELECT PrimaryFanLocationsTbl.FanIndexID, PrimaryFanLocationsTbl.LocationID, PrimaryFanLocationsTbl.DateOfMove" & _
                                        " FROM PrimaryFanLocationsTbl" & _
                                        " WHERE PrimaryFanLocationsTbl.LocationID='" & LocID & "' AND PrimaryFanLocationsTbl.DateOfMove< #" & Format(TestDte, "mm/dd/yyyy") & "#;", dbOpenSnapshot)

thanks

[Missinglinq]

While

TestDte

is defined as DateTime

Format(TestDte, "mm/dd/yyyy")

is Text! That's because the Format() Function returns a String. Try treating it as Text, in your code, and see what happens.

Linq ;0)>

[pbaldy]

What's the data type of LocationID?

[max]

-Missingling, I have also tried not reformatting TestDte, I get the same error

[max]

-pbaldy, LocationID is a string but the error message specifies that the mismatch is in the criteria phrase

[pbaldy]

Can you post the db here?

[max]

How do I do that?

[max]

Should I post the entire db or just all the code for the module?

[pbaldy]

That table and code are most relevant.

[max]

here is Table and Code! I have never run the code past line 9 (Set Rst4......)

thanks

Sub ViewOemData() ' selects design fan curve data and enters to spreadsheet

Set wsheet = appExcel.Worksheets(LocationName)


cnt3 = 0
                                                        'picks data for system curve
    P1 = wsheet.Cells(25, 20) * 1000
    Q1 = wsheet.Cells(15, 2)


Set wsheet = appExcel.Worksheets("Fan Data")
                                                        'enters data for system curve to spreadsheet
    wsheet.Cells(8, 3 * NoCurves) = Q1
    wsheet.Cells(9, 3 * NoCurves) = P1
    
    'SQLDate = Format$(TestDte, "#\mm\/dd\/yyyy\#") ' TRYING TO FIX DATA TYPE MISMATCH




    'finds fan at test location on test date by selecting all dates of moves preceding date of test and putting into ascending order

Set Rst4 = CurrentDb.OpenRecordset("SELECT PrimaryFanLocationsTbl.FanIndexID, PrimaryFanLocationsTbl.LocationID, PrimaryFanLocationsTbl.DateOfMove" & _
                                        " FROM PrimaryFanLocationsTbl" & _
                                        " WHERE PrimaryFanLocationsTbl.LocationID='" & LocID & "' AND PrimaryFanLocationsTbl.DateOfMove< #" & Format(TestDte, "mm/dd/yyyy") & "#;", dbOpenSnapshot)
                                        
                                            With Rst4
                                            
                                            .MoveFirst
                                            FanIndexIDVar = .Fields(0)
                                            PrecedingMoveDate = .Fields(2)
                                            
                                            End With
                                            
                                            
    ' takes fan move date, finds preceding configuration date and selects configuration data

Set Rst10 = CurrentDb.OpenRecordset("SELECT FanConfigurationB4DateQry.FanIndexID, Last(FanConfigurationB4DateQry.ConfigurationDate) AS LastOfConfigurationDate" & _
                                    "FROM ( SELECT FanConfigurationTbl.FanIndexID, FanConfigurationTbl.ConfigurationDate FROM FanConfigurationTbl WHERE (((FanConfigurationTbl.FanIndexID)= " & FanIndexIDVar & ") AND ((FanConfigurationTbl.ConfigurationDate)< " & PrecedingMoveDate & " ));)" & _
                                    "GROUP BY FanConfigurationB4DateQry.FanIndexID;", dbOpenSnapshot)
         
         
         With Rst10
            
         
             wsheet.Cells(6, 3 * NoCurves - 1) = .Fields(1)
             wsheet.Cells(41, 3 * NoCurves - 1) = .Fields(1)
             wsheet.Cells(12, 3 * NoCurves - 1) = .Fields(0)
             
             Do While Not .EOF
             
                 wsheet.Cells(44 + cnt3, 3 * NoCurves - 1) = .Fields(3)
                 wsheet.Cells(44 + cnt3, 3 * NoCurves - 1) = .Fields(4)
                 
                 cnt3 = cnt3 + 1
                 
                 .MoveNext
        
             Loop
             
         End With
         
Set Rst10 = Nothing
PrecedingMoveDate = ""
         
        
End Sub

[pbaldy]

Do you want to change your answer regarding LocationID?

it's a numeric data type.

[pbaldy]

I should have added that the numeric data means you don't want the surrounding apostrophes. I suspect your confusion is because of a lookup field:

http://access.mvps.org/access/lookupfields.htm

[max]

Thank-you very much, my code has progressed and I understand what the problem was.

Now however I get a 'No Current record' error at line 13. I have run this code as a query within access and I extract the expected records. Any Ideas?

[max]

No worries I have managed to fix it now.

Thanks again

[pbaldy]

No problem.