Trying To Extract Data From Access Using Excel VBA

[RunTime91]

Greetings All...

Both code snippets are written in Excel VB Editor and are designed to simply extract data from Access and place it on a specific Excel sheet in a Specific Range

The below code works perfectly when I hard code the date

Sub RefreshData3()Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim RecCount As Long
Dim SQL As String

'Setup Reference To Workbook And Sheet
Set xlBook = ActiveWorkbook
Set xlSheet = xlBook.Worksheets("Sheet1")
Application.ScreenUpdating = False
xlSheet.Range("Q7:Z9").Clear
'Communicate With User
Application.StatusBar = "Connection to an External Database..."
'Application.Cursor = xlWait
Set Db = OpenDatabase(DbLoc)
        
StrSQL = "SELECT vProductionDetails.Entered, Sum(IIf([CaseStatusDesc]= ""DMS"",1,0)) AS DMS, Sum(IIf([CaseStatusDesc]= ""EDI"",1,0)) AS EDI, " & _
            "Sum(IIf([CaseStatusDesc]= ""WARF"",1,0)) AS WARF, Sum(IIf([CaseStatusDesc]= ""Exceptions"",1,0)) AS EXC, " & _
              "Sum(IIf([CaseStatusDesc]= ""R&R"",1,0)) AS [R&R] " & _
        "FROM vProductionDetails " & _
        "WHERE (((vProductionDetails.DB_ID) Not Like ""M*"") AND ((vProductionDetails.Special)=1)) " & _
        "GROUP BY vProductionDetails.Entered " & _
        "HAVING (((vProductionDetails.Entered) = ""12-19-2018"")) " & _
        "ORDER BY vProductionDetails.Entered DESC;"

'Execute Query
Set Rs = Db.OpenRecordset(StrSQL, dbOpenSnapshot)

'Copy RecordSet to SpreadSheet
Application.StatusBar = "Writing to SpreadSheet..."
If Rs.RecordCount = 0 Then
  Msgbox "There Are No Records To Retrieve", vbInformation + vbOKOnly, "No Data"
  GoTo SubExit
  
Else
  Rs.MoveLast
  RecCount = Rs.RecordCount
  Rs.MoveFirst
End If
xlSheet.Range("Q7:Z7").CopyFromRecordset Rs

Once I had the above working I assigned the 'Date' to a variable using a date (same date - 12/19/2018) plugged in the one of the Excel sheets
With the code below I get the "There Are No Records To Retrieve" error (which is an error 'cause there are records)

Sub RefreshData3()
On Error GoTo SubError
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Dim RecCount As Long
Dim SQL As String

Dim RunDate As Date
RunDate = Sheets("DataEntry").Range("D4")

'Setup Reference To Workbook And Sheet
Set xlBook = ActiveWorkbook
Set xlSheet = xlBook.Worksheets("Sheet1")
Application.ScreenUpdating = False
xlSheet.Range("Q7:Z9").Clear
'Communicate With User
Application.StatusBar = "Connection to an External Database..."
'Application.Cursor = xlWait
Set Db = OpenDatabase(DbLoc)

StrSQL = "SELECT vProductionDetails.Entered, Sum(IIf([CaseStatusDesc]= ""DMS"",1,0)) AS DMS, Sum(IIf([CaseStatusDesc]= ""EDI"",1,0)) AS EDI, " & _
            "Sum(IIf([CaseStatusDesc]= ""WARF"",1,0)) AS WARF, Sum(IIf([CaseStatusDesc]= ""Exceptions"",1,0)) AS EXC, " & _
              "Sum(IIf([CaseStatusDesc]= ""R&R"",1,0)) AS [R&R] " & _
        "FROM vProductionDetails " & _
        "WHERE (((vProductionDetails.DB_ID) Not Like ""M*"") AND ((vProductionDetails.Special)=1)) " & _
        "GROUP BY vProductionDetails.Entered " & _
        "HAVING ((vProductionDetails.Entered) = " & RunDate & ") " & _
        "ORDER BY vProductionDetails.Entered DESC;"
        
Set Rs = Db.OpenRecordset(StrSQL, dbOpenSnapshot)

'Copy RecordSet to SpreadSheet
Application.StatusBar = "Writing to SpreadSheet..."
If Rs.RecordCount = 0 Then
  Msgbox "There Are No Records To Retrieve", vbInformation + vbOKOnly, "No Data"
  GoTo SubExit
  
Else
  Rs.MoveLast
  RecCount = Rs.RecordCount
  Rs.MoveFirst
End If
xlSheet.Range("Q7:Z7").CopyFromRecordset Rs

I've tested the Date variable in the Immediate - It's correct
I've surrounded the date in the SQL string with every character combination I can think of (single & double quotes, double-double quotes, hashtags, hashtags & quotes - you get the idea)
Still all I can return is the No Records error...


But clearly I'm missing something and that is what brings me here

Help?

Thanks so much...

[CJ_London]

I presume the line you are having a problem with is this one

"HAVING ((vProductionDetails.Entered) = " & RunDate & ") " & _

One of the problems with excel is it formats everything so you do not see the actual data. It also is fairly carefree with data types.

So you need to ensure the datatype is correct and the value is correct.

try

"HAVING ((vProductionDetails.Entered) = #" & format(RunDate,"mm/dd/yyyy") & "#) " & _

the format ensures the value is correct, the # ensures the datatype is correct

[Micron]

you're saying you tried
= #" & RunDate & "#) and it didn't work? Does the format of the sheet date value jive with your regional settings? Wondering about the dash as a date separator.

[CJ_London]

Wondering about the dash as a date separator.

I wondered about that, but the OP said this worked

"HAVING (((vProductionDetails.Entered) = ""12-19-2018"")) " & _

but then I wondered about the double double quotes, but if it works, it works - I would have expected a compile error

Also I do know the format 'yyyy-mm-dd' works

[RunTime91]

Ajax - Micron ~ Thank You so much for throwing down on this one

I'm at a loss...

I tried the following:

#" & RunDate & "#
#" & format(RunDate,"mm/dd/yyyy") & "#)
#" & format(RunDate,"mm-dd-yyyy") & "#)
#" & format(RunDate,"yyyy-mm-dd") & "#)

Each attempt pulled the No Records Error

Now, I did check the formats and sure enough the 'Entered' field in the Access table is formatted as Short Text - FTLOGod I have no idea why someone would do that
And of course the Excel field is formatted as Date.

So I changed the variable from Date to String and played around with single, double quotes and hashtags
Had high hopes - but still the No Records Error

Thoughts?

[June7]

Did you try:

RunDate declared as string, use apostrophe delimiters (I prefer instead of doubled quote marks):

"HAVING vProductionDetails.Entered = '" & Format(RunDate, "12-19-2018") & "'" & _

RunDate declared as a date value, convert the Access field value to true date:

"HAVING CDate(vProductionDetails.Entered) = #" & RunDate & "#" & _

[RunTime91]

Boom!

This is simply the BEST Access Forum on the Net to get help with stuff like this

After trying so many things I had become blind to things I hadn't tried and absent minded to the things I did try..

If not for the three of you I would still be spinning my wheels

Thank You, so very much Micron, Ajax & June!!

Here is the solution that worked best. Of course, re-declaring RunDate as a String & the following

"HAVING (((vProductionDetails.Entered) = '" & Format(RunDate,"mm/dd/yyyy") & "'));"

Since this data will be extracted for only a single date range - I removed the ORDER BY clause as well

Again - Thank You to each of you...

Now, on to the next...

[Micron]

You're welcome and glad we could help, although in terms of that, should have put my name last?
Other muses: I also would use ='" & someString & "' instead of "" as I find groups of double quotes can be temperamental; expecting """ & someStringI & """ would have been required in your case. As for the sql, I'm not seeing a need for Having as opposed to simply Where because you're not using any aggregate function on RunDate. You might find that making RunDate part of the Where clause runs faster if you've got a lot of records to search through.

[RunTime91]

Hey Micron....
After a simple review I agree on adding the Date criteria [Entered] to the WHERE... So I added it but it didn't work; I removed the GROUP BY and tried again - still nothing - Pull Entered from the SELECT statement - Not happening... The code below gives the least verbose error response which makes me think, once again, I'm close but can't quite get the punctuation correct...(and yes, I've moved the paren's around as well)

StrSQL = "SELECT vProductionDetails.Entered, Sum(IIf([CaseStatusDesc]= ""DMS"",1,0)) AS DMS, Sum(IIf([CaseStatusDesc]= ""EDI"",1,0)) AS EDI, " & _
            "Sum(IIf([CaseStatusDesc]= ""WARF"",1,0)) AS WARF, Sum(IIf([CaseStatusDesc]= ""Exceptions"",1,0)) AS EXC, " & _
              "Sum(IIf([CaseStatusDesc]= ""R&R"",1,0)) AS [R&R] " & _
        "FROM vProductionDetails " & _
        "WHERE (((vProductionDetails.DB_ID) Not Like ""M*"") AND ((vProductionDetails.Special)=1) AND ((vProductionDetails.Entered) = '" & Format(RunDate, "mm/dd/yyyy") & "'));"

As for your name being last? Nah, honestly, as helpful as this site has been for me everybody's name should be first, but it is kind of hard when thanking multiple folks -
Anyway, since I completely agree with you on the WHERE placement of the RunDate I'm going to keep poking around with the above, meanwhile if you have a moment to take a look I would as always appreciate it.
Thanks Micron...

Edit: The Error I'm receiving is a 3122: Query does not include the specified expression 'Entered' as part of an aggregate function

[CJ_London]

if you are summing, you need to group by the other fields you are selecting (i.e. not only in criteria)

[RunTime91]

Ahhh, I completely understand - providing a little more info (the entire SQL Statement e.g.) always helps..

Thanks Ajax...

[CJ_London]

If you are not sure, use the query builder to get it how you want, then look at the sql view and adapt to run in VBA

[Micron]

agree with the Group By comment, but am having second thoughts. You have enclosing single quotes ' around what looks like a date
'" & Format(RunDate, "mm/dd/yyyy") & "'));"
and the message seems to be pointing to that as being the problem. However, IIRC the format function converts a value to a string, in which case the quotes would make sense, but not if the field data type is Date/Time. You could try removing them and see what happens. Was just playing with that now, but I have the disadvantage of not having your db to test with. I also noticed that when I used Format(9/27/2017,"mm/dd/yyyy") Access insists on making it part of a HAVING clause. Note that that example is from the query sql view itself, which of course has to differ slightly from sql created in code with respect to quotes and such.

If you want to provide a test db with some data and the query, I/we can see what can be made of it. On the other hand, if you've got it working in one fashion or another, then stick with what works.

[June7]

Review https://www.programmerinterview.com/...-where-clause/