Hi Ajaar,
I have made some corrections/additions in your procedure. Please see attached file.
I have added a new Procedure and changed the name to AllbyDateandUserNew.
Please note the following when using dates as criteria in SQL.
In VBA, when date literals are concatenated with SQL strings, only US format 'm/d/yyyy' is valid and natively used by JET SQL. Otherwise there may be unpredictable results.
Given below the corrections I have made in code.
Code:
sQRY = "SELECT QryDataByRequestNo.* FROM " & QryDataByRequestNo
sQRY = sQRY & " WHERE ((Data.[RequestDate]) Between #" & df & "# And #" & dt & "#"
sQRY = sQRY & " AND ((Data.[CreatedBy]) = " & user1 & " ));"
'modified to
sQRY = "SELECT QryDataByRequestNo.* FROM QryDataByRequestNo "
sQRY = sQRY & "WHERE ((Data.[RequestDate]) >= #" & SQLDate(df) & "# <= #" & SQLDate(dt) & "# "
sQRY = sQRY & "AND ((Data.[CreatedBy]) = '" & user1 & "' ));"
Query name QryDataByRequestNo is outside the sql string. It must be inside the string. Also User1 is Text/String data type. So it must be enclosed in quotes.
Replaced Between and And operator with >= and <=. Sometimes for date criterias, Between operator may not work as intended. This is based on my experience. I always use >= and <=.
Also I have used a wrapper function SQLDate to convert the start and end dates to the format natively used by JET SQL. This function has been adapted from
http://www.everythingaccess.com/tutorials.asp?ID=International-Dates-in-Access
Code:
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
'modified to
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly, adCmdText
You must give all the parameters while opening ADO recordset though ADO Help says all parameters are optional. The last parameter Options is missing in your code. When data source (first parameter) is SQL string, the last parameter must be adCmdText. If data source is Access Table/Query, the last parameter must be adCmdTable. If data source type and last parameter are not matching, ADO will throw error.
But while opening DAO recordset, even if parameters are missing, DAO will open the recordset without any issue.
Also I have added Error Handling code. With out error handling, there are high chances of file corruption.
Additional info while using dates as criteria in Excel formulas:
If user has set date format as 'd/m/yy' in Regional Settings, all Windows Applications like Excel, Access, etc evaluate dates like 5/1/2015 as 5th Jan and not May 1 in user interface.
When dates are used as criteria in Excel formulas, it is safe to use this format dd/mmm/yyyy. Otherwise a user who has set d/m/yyyy in Regional Settings and intend to use a date 5th Jan as criteria in a formula would enter 5/1/2015. When this file is sent to another user who has set US format m/d/yyyy in his machine, he may see different results as Excel in US format machine would evaluate the date as May 1, 2015.
So in Excel formulas, if you use dd/mmm/yyyy format for date criterias, it would be evaluated correctly irrespective of Regional settings or user locale.
Regards,
Surendran