By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,276 Members | 1,951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,276 IT Pros & Developers. It's quick & easy.

adodb recordset not returning results

P: 4
Hi,

Im having an issue with returning records from an access database using vba. My recordset will generate data for some dates but not others (all dates contain data). Any ideas?

Expand|Select|Wrap|Line Numbers
  1.     Dim conn As Object
  2.     Dim userID As String
  3.     Dim actDate As String
  4.     Dim x As Integer
  5.     Dim DeleteId As Range
  6.     Dim DeleteVolume As Range
  7.  
  8.     userID = Sheets("Input").Cells(3, 2).Value
  9.     actDate = Sheets("Input").Cells(5, 4).Value
  10.     actDate = Format(actDate, "dd/mm/yyyy")
  11.  
  12.     Set conn = CreateObject("ADODB.Connection")
  13.     Dim objCmd As Object
  14.     Set objCmd = CreateObject("ADODB.Command")
  15.     Dim objRs As Object
  16.     Set objRs = CreateObject("ADODB.Recordset")
  17.  
  18.     objCmd.CommandText = _
  19.         "SELECT record.processID, record.recordQuantity " & _
  20.         "FROM record " & _
  21.         "WHERE record.recordUserID = " & userID & _
  22.         " AND record.recordDate = #" & actDate & "#"
  23.     objCmd.CommandType = 1
  24.  
  25.     conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
  26.         "Data Source=" & Application.ActiveWorkbook.Path & _
  27.         "\QualityDatabase.accdb;"
  28.  
  29.     objCmd.ActiveConnection = conn
  30.  
  31.     Set objRs = objCmd.Execute
  32.  
  33.     x = 1
  34.     Debug.Print "Start of Output"
  35.     Do While Not objRs.EOF
  36.         Worksheets("workerSheet").Cells(x, 1).Value = objRs(0)
  37.         Worksheets("workerSheet").Cells(x, 2).Value = objRs(1)
  38.         If Worksheets("workerSheet").Cells(x, 2).Value = 0 Then
  39.         Set DeleteId = Worksheets("workerSheet").Cells(x, 1)
  40.         Set DeleteVolume = Worksheets("workerSheet").Cells(x, 2)
  41.         DeleteId.Delete ([xlShiftUp])
  42.         DeleteVolume.Delete ([xlShiftUp])
  43.         Else
  44.         x = x + 1
  45.         End If
  46.         objRs.MoveNext
  47.  
  48.     Loop
  49.  
  50.     Set conn = Nothing
  51.     Set ObjRs = Nothng
  52.     Set ObjCmd = Nothing
2 Weeks Ago #1

✓ answered by twinnyfo

Try to keep in mind that a "Date" is a numerical value, so the format simply displays that numerical value in a format that we can easily understand.

Try using the format "yyyy-mm-dd" for actDate and see what happens.

Share this Question
Share on Google+
7 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,364
Akamatsu13,

Welcome to Bytes!

You describe your problem:
My recordset will generate data for some dates but not others
but you don't provide specific examples, which would be tremendously helpful.

I do see two potential problems on the surface, which may or may not affect things.

First, your variable actDate is a String. If the value in Cells(5, 4) is a date (which it ought to be--I am assuming this is in Excel), then just make that variable a date data type, then you don't need to convert that into a "string date".

Second, when you convert your string into a string that "looks like a date," you are using the format of "dd/mm/yyyy", which works on many systems, but not all. The universal date string is "yyyy-mm-dd". However, back to my first point, if you are able to translate this variable actDate from whatever it was into a string that "looks like a date," then this value, before it is converted IS A DATE. So, just use the value as is.

However, concerning the actual nature of your question, let us know which dates are recognized and which ones are not. This can lead us down a path toward solution.

Hope this hepps!
2 Weeks Ago #2

P: 4
Hi Twinnyfo,

Thanks for the response.

I have switched the variable to a date data type and removed the conversion code. However it still runs as it did previously.

Dates prior to the 13th of a given month appear to be the ones not recognised.
2 Weeks Ago #3

twinnyfo
Expert Mod 2.5K+
P: 3,364
This must have to do with date format. What is the data type in the database? If you are having problems with dates prior to the 13th, that implies that it sees the day as the month.

Is it possible to Debug.Print the CommandText value, and cut and paste that here so we can take a look at it?

Thanks.
2 Weeks Ago #4

P: 4
The data type in the database is Date/Time with format "dd/mm/yyyy"

CommandText value is:
"SELECT record.processID, record.recordQuantity FROM record WHERE record.recordUserID = 182 AND record.recordDate = #03/01/2020#"
2 Weeks Ago #5

twinnyfo
Expert Mod 2.5K+
P: 3,364
Try to keep in mind that a "Date" is a numerical value, so the format simply displays that numerical value in a format that we can easily understand.

Try using the format "yyyy-mm-dd" for actDate and see what happens.
2 Weeks Ago #6

P: 4
That has fixed the issue! Thank you very much for your help.
2 Weeks Ago #7

twinnyfo
Expert Mod 2.5K+
P: 3,364
I'm glad I could hepp! Dates can sometimes be tricky in MS Access.

Let us know if we can provide any more hepp!
2 Weeks Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.