Change Dates in an Access Query from Access form and overwrite data in existing Excel file 2

[kimmer7389]

This is what I am trying to do:

On an Access form:
1. User selects a start weekending date from a combobox
2. User Selects an end weekending date from a combobox.
3. User clicks a button to start the process.

The process runs a query in Access with the dates the user selected and overwrites data in an existing Excel file.

Below is my code. I am getting a runtime error "3075" and the message "Syntax error in date in query" expression". I have tried several different ways of writing the SQL String and I am at a loss. Any ideas? Am i even close to doing what I want?

Private Sub CmdRunReport_Click()

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
Set dbs = CurrentDb

' Selects the data in the query
strSQL = "SELECT * FROM ActualsWeeklyQuery " & _
   "WHERE ActualsWeeklyQuery.WeekendingDate Between #" & ComboWeekendingDateStart & "#" & " And #" & ComboWeekendingDateEnd & "#;"


strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

' Exports data to Excel file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF, "C:\ExcelTest.xls"

dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing


End Sub

 

[kimmer7389]

So I have been working on it and my code now looks like the below.

Code runs without errors but the Excel file is empty. No records.

Private Sub CmdRunReport_Click()

Dim dbs As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String
Dim strQDF As String
Set dbs = CurrentDb

SDate = Format(ComboWeekendingDateStart.Value, "mm/dd/yyyy") 'Start Date
EDate = Format(ComboWeekendingDateEnd.Value, "mm/dd/yyyy")   'End/Close Date


' Selects the data in the query

 strSQL = ("SELECT * FROM [ActualsWeeklyQuery]")
    strSQL = strSQL & " WHERE (([ActualsWeeklyQuery].[WeekendingDate]) "
    strSQL = strSQL & "BETWEEN #" & SDate & "# AND #" & EDate & "#); "

strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
qdfTemp.Close
Set qdfTemp = Nothing

' Exports data to Excel file
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
      strQDF, "C:\ExcelTest.xls"

dbs.QueryDefs.Delete strQDF
dbs.Close
Set dbs = Nothing


End Sub

 

[SkipVought]

What are the actual values in Sdate & Edate?

Use Dubug.Print

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[trevil620]

I tinkered with this and found some strange (but normal for Access behavior).

Does your date fields contain a time also, or is it only a date?

1. I would define variables SDate and EDate like 'Dim SDate as Date', etc.
2. The table I tested with has Date AND Time values in a field that is defined as 'Date/Time'
3. I changed the query build to use Date/Time
4. My table has 81 records, but if I select range '11/21/2006 11:27:17 AM' and '11/21/2006 11:37:44 AM' I should have retrieved 2 records, but only one was selected.
5. I manually changed the SQL and dropped one second from the SDate and added one second to the EDate. Then I got the 2 records I expected.

Finally, I added three new records to the table; none had a time specified; and my test then selected all three records.

It seems 100 years ago I ran into this exact same issue, but unfortunately can't remember what the solution was.

 

[trevil620]

Here is an explanation from a Microsoft person....

http://bytes.com/topic/access/answers/202134-problem-use-between-query

 

[SkipVought]

There is no variable type of date/time!

Date variables store date/time data.

Date/time values are NUMBERS. The units of date/time is DAYS, so that the integral part is date (full days) and the fractional part is time (part of a day).

So the question remains: what are the actual values in Sdate & Edate?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[kimmer7389]

Ahhh so something is going on with the date formatting. When I debug.print for Sdate and Edate I got:

12/31/1899
12/31/1899

 

[trevil620]

Skip, I could have been more explicit, but I didn't say there was a "variable type of date/time".
What I did say is there is a table with a field (used to store dates) that is defined as a data type of "date/time".

Kimmer, what is the "data type" of the field in your table?
And re the "12/31/1899"... just a wild chance... does your combo box have multiple columns and are you sure you got the correct column? I believe a zero would translate to 12/31/1899.
I would add a debug.print of the two combo box values used (i.e. ComboWeekendingDateStart.Value..."

 

[kimmer7389]

The fields are Date/Time fields. I think it is the combobox using the 0. There are 2 columns in the drop down lists. If I type my dates into my code it works just as it should.

 

[kimmer7389]

Its working!!!!

SDate = Format(ComboWeekendingDateStart.Column(1), "mm/dd/yyyy") 'Start Date
EDate = Format(ComboWeekendingDateEnd.Column(1), "mm/dd/yyyy") 'End/Close Date

Thanks for your help! Sometimes I just need a little bit of clue.