0

I have a recordset that works fine in my instance of Microsoft Access, but when I try it out on another computer, it is failing.

I already have plenty of recordsets running in both versions of my Access application. Both are on Access 2010, but they likely have different drivers.

The code looks like this:

     Set rsWeek = CurrentDb.OpenRecordset("SELECT * FROM [Timecards] 
     WHERE employeeID = " & empID & " AND workDate BETWEEN #" & Me.Text23 
     & "# AND #" & Me.Text25 & "#", dbOpenSnapshot)

I've used a MsgBox to confirm all of my variables work. The code works as planned on my first computer, but the second one simply fails with a generic "On Click" error.

It appears the issue here is the BETWEEN part, since this is the only time I've ever used BETWEEN.

Is there some sort of SQL driver that my second computer needs to be able to run this query with BETWEEN in it?

braX
  • 11,506
  • 5
  • 20
  • 33
Danchat
  • 137
  • 2
  • 12
  • 3
    Just [use parameters](https://stackoverflow.com/q/49509615/7296893)! This is likely an issue with different time formats on different computers, if you use parameters it won't occur. – Erik A Aug 09 '19 at 17:26
  • I just finished reading your post on Parameters, but I'm sure what you want me to do here. The time format is the same on this other computer (as far as I can tell), and I have several other queries/recordsets that use dates on both computers and they all work without any problems. – Danchat Aug 09 '19 at 18:28
  • 1
    After checking your profile, it seems you are using a SQL-Server as backend? And some machines have different ODBC drivers (sql server 10.xxx). SQL-Server newer than 2008 and you use`datetime2`on`workDate`? To make the trouble perfect, some users use the`Ms Access Runtime 2010`and you aren't aware of https://stackoverflow.com/a/56640798/9439330 ? – ComputerVersteher Aug 10 '19 at 15:50
  • Thanks for posting this, I followed the link and I tried out what Paul TIKI said he did, and this actually solved the problem. The solution was to bring the .accdb file to the second computer, remove a broken reference, Repair and Compact the code, and then once it was exported to .accde everything worked just fine. I'll test out the other answers submitted to this next, but this seems to be what was wrong. – Danchat Aug 12 '19 at 15:03

4 Answers4

2

You must format your date value as text representations:

Set rsWeek = CurrentDb.OpenRecordset("SELECT * FROM [Timecards] 
WHERE employeeID = " & empID & " AND workDate BETWEEN #" & Format(Me!Text23.Value, "yyyy\/mm\/dd") & "# AND #" & Format(Me!Text25.Value, "yyyy\/mm\/dd") & "#", dbOpenSnapshot)

Edit:

If you have followed Albert's advice and it still fails, then try:

Set rsWeek = CurrentDb.OpenRecordset("SELECT * FROM [Timecards] 
WHERE employeeID = " & empID & " AND workDate BETWEEN #" & Format(DateValue(Me!Text23.Value), "yyyy\/mm\/dd") & "# AND #" & Format(DateValue(Me!Text25.Value), "yyyy\/mm\/dd") & "#", dbOpenSnapshot)

or, if a textbox can be empty:

Set rsWeek = CurrentDb.OpenRecordset("SELECT * FROM [Timecards] 
WHERE employeeID = " & empID & " AND workDate BETWEEN #" & Format(Nz(Me!Text23.Value, Date()), "yyyy\/mm\/dd") & "# AND #" & Format(Nz(Me!Text25.Value, Date()), "yyyy\/mm\/dd") & "#", dbOpenSnapshot)
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Using this code, I got the same error. I don't think the date is the issue here - I already have plenty of code running fine between my two computers involving dates. – Danchat Aug 09 '19 at 18:29
  • 2
    make sure you set those un-bound text boxes as date format. Then using the above example code should work. If you don't do this, then a regional date setting on the users computer will mess this up. The result is your queries will work on all computers - regardless of the user's date settings. So, the user's PC date settings can mess things up. If you force/set the textboxes to date format, then the above approach should work - even for computers with different date formats. – Albert D. Kallal Aug 10 '19 at 03:28
  • Thanks for the help. This didn't fix my issue, but Albert, your solution [here](https://stackoverflow.com/questions/56584143/access-front-end-works-fine-for-most-users-but-one-user-keeps-having-a-variety-o/56640798#56640798) helped me find the true solution. – Danchat Aug 12 '19 at 15:11
1

As suggested by @ErikA in the comments, using parameters instead of concatenating form values in your SQL statement would avoid all issues arising from regional differences, and would also protect your code from the possibility of SQL injection.

Consider the following example based on the content of your current code:

With CurrentDb.CreateQueryDef _
("", "select * from timecards t where t.employeeid = @id and t.workdate between @from and @to")
    .Parameters("@id") = empID
    .Parameters("@from") = DateValue(Me.Text23)
    .Parameters("@to") = DateValue(Me.Text25)
    With .OpenRecordset(dbOpenSnapshot)
        If Not .EOF Then
            .MoveFirst
            Do Until .EOF
                Debug.Print .Fields(0)
                .MoveNext
            Loop
        End If
        .Close
    End With
End With

The above will simply print the value of the first field in the recordset, just to demonstrate the method.

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
0

Please try this way, according to my experience it works better.

Set rs = CurrentDb.OpenRecordset("query string", dbOpenDynaset, dbSeeChanges)
Xilmiki
  • 1,453
  • 15
  • 22
0

ComputerVersteher pointed me in the right direction by pointing me to this issue. I tried Compact and Repair Database, which threw an error on my second computer. It turns out there was a reference missing on my second computer, "Microsoft Windows Common Controls 6.0 (SP6)". After that I did the Compact and Repair function and I tested it out again and it worked. I can't say I know why it crashed only on the one query, but the problem is gone now.

Danchat
  • 137
  • 2
  • 12