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?

Improve this question
4
  • 3
    Just use parameters! This is likely an issue with different time formats on different computers, if you use parameters it won't occur. Commented Aug 9, 2019 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. Commented Aug 9, 2019 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 usedatetime2onworkDate? To make the trouble perfect, some users use theMs Access Runtime 2010and you aren't aware of stackoverflow.com/a/56640798/9439330 ? Commented Aug 10, 2019 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. Commented Aug 12, 2019 at 15:03

4 Answers 4

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
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.
Thanks for the help. This didn't fix my issue, but Albert, your solution here helped me find the true solution.
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.

Improve this answer

1 Comment

Thanks a bunch for this example, I'll definitely try this out.
0

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

Set rs = CurrentDb.OpenRecordset("query string", dbOpenDynaset, dbSeeChanges)
Improve this answer

Comments

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.