• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Run a access query from excel with multiple parameter

ajaar

Member
Dear Friends,

I am trying to run a access query from VBA excel with multiple parameters. It is working when there is only one parameters.
when it is multiple Parameters, I am getting error “Syntax error in query expression”. Please correct me in the below what i am doing wrong.
Code:
  sQRY = "SELECT QryDataByRequestNo.* FROM " & QryDataByRequestNo & " " & vbNewLine
  sQRY = "WHERE ((Data.[RequestDate]) Between #" & df & "# And #" & dt & "#" & " " & vbNewLine
  sQRY = sQRY & "AND ((Data.[name1]) = " & user1 & " ));"


Thanks
Ajaar
 
@ajaar

It looks as though you simply need to put sQRY & before the WHERE statement as well.

Hope that helps.

Regards,
Ken
 
Hi Ken,

thank you for your time.

I did the charge, still i am getting error "Syntax error in FROM clause". i cant make out the error. Please check this.

Regards
Ajaar.
 
Pls check this...

Code:
sQRY = "SELECT QryDataByRequestNo.* FROM " & QryDataByRequestNo
  sQRY = sQRY & " WHERE ((Data.[RequestDate]) Between #" & df & "# And #" & dt & "#"
  sQRY = sQRY & " AND ((Data.[name1]) = " & user1 & " ));"
 
Dear Deepak,

Thank you for looking into this.

Still i am getting same error "Syntax error in FROM clause".
for your information below is the variable i stored.

df = Sheets("r status").Range("w4").Value
dt = Sheets("r status").Range("x4").Value
user1 = Sheets("Home").Range("c20").Value

Thanks
Ajaar
 
Dear Deepak,

Thank you for looking into this.

Still i am getting same error "Syntax error in FROM clause".
for your information below is the variable i stored.

df = Sheets("r status").Range("w4").Value
dt = Sheets("r status").Range("x4").Value
user1 = Sheets("Home").Range("c20").Value

Thanks
Ajaar


Can i have the sample workbook with complete code!!
 
Dear Deepak,

Please find the attachment here with.

Thanks
Ajaar
 

Attachments

  • Master.xlsm
    21.5 KB · Views: 0
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
 

Attachments

  • Master.xlsm
    25.4 KB · Views: 3
Dear Surendran,
Thank you so much for your help, really thank full to your effort. i could learn new things.
after testing with new code, i am getting error by error handler. Please find the attachment.

Thanks
Ajaar
 

Attachments

  • Error-VBA.xlsx
    35.9 KB · Views: 0
Hi Ajaar,

Sorry. SQLDate wrapper function is returning the date string with #. I simply overlooked. Modify the SQL as given below.

Code:
'current
sQRY = "SELECT QryDataByRequestNo.* FROM  QryDataByRequestNo "
sQRY = sQRY & "WHERE ((Data.[RequestDate]) >= #" & SQLDate(df) & "# <= #" & SQLDate(dt) & "# "
sQRY = sQRY & "AND ((Data.[CreatedBy]) = '" & user1 & "' ));"
 
'to
'modified SQL
sQRY = "SELECT QryDataByRequestNo.* FROM QryDataByRequestNo "
sQRY = sQRY & "WHERE ((Data.[RequestDate]) >= " & SQLDate(df) & " <= " & SQLDate(dt) & " "
sQRY = sQRY & "AND ((Data.[CreatedBy]) = '" & user1 & "' ));"

Regards,
Surendran
 
Hi Ajaar,

one more correction in SQL. ('And Data.[RequestDate]' was missing)
Use this SQL.

Code:
sQRY = "SELECT QryDataByRequestNo.* FROM  QryDataByRequestNo "
sQRY = sQRY & "WHERE Data.[RequestDate] >= " & SQLDate(df) & " And "
sQRY = sQRY & "Data.[RequestDate] <= " & SQLDate(dt) & " "
sQRY = sQRY & "AND Data.[CreatedBy] = '" & user1 & "';"
 
[

Regards,
Surendran
 
Back
Top