SQL syntax problem.

[LEXCERM]

Hi there,

Having problem with this parameter query which works off of a form and don't know how to resolve. Error being:-
"Run-Time error 2342...A RunSQL action requires an argument consisting of an SQL statement."

Dim mysql
mysql = "SELECT tbl_pallet_log.ID, tbl_pallet_log.Entry_Date, tbl_pallet_log.order_Number, tbl_pallet_log.Customer_No, tbl_pallet_log.Customer, tbl_pallet_log.Postcode, tbl_pallet_log.Pallets " & _
        "FROM tbl_pallet_log " & _
        "GROUP BY tbl_pallet_log.ID, tbl_pallet_log.Entry_Date, tbl_pallet_log.order_Number, tbl_pallet_log.Customer_No, tbl_pallet_log.Customer, tbl_pallet_log.Postcode, tbl_pallet_log.Pallets " & _
        "HAVING (((tbl_pallet_log.Entry_Date) Between [Forms]![frm_customer_pallet_report]![txt_date_from] And [Forms]![frm_customer_pallet_report]![txt_date_to]));"
DoCmd.RunSQL mysql

Thanks in advance.
Paul.

 

[KenHigg]

You may have to do something like:

Dim mysql
mysql = "SELECT tbl_pallet_log.ID, tbl_pallet_log.Entry_Date, tbl_pallet_log.order_Number, tbl_pallet_log.Customer_No, tbl_pallet_log.Customer, tbl_pallet_log.Postcode, tbl_pallet_log.Pallets " & _
        "FROM tbl_pallet_log " & _
        "GROUP BY tbl_pallet_log.ID, tbl_pallet_log.Entry_Date, tbl_pallet_log.order_Number, tbl_pallet_log.Customer_No, tbl_pallet_log.Customer, tbl_pallet_log.Postcode, tbl_pallet_log.Pallets " & _
        "HAVING (((tbl_pallet_log.Entry_Date) Between #" & [Forms]![frm_customer_pallet_report]![txt_date_from] & "# And #" & [Forms]![frm_customer_pallet_report]![txt_date_to] & "#));"

???

 

[Rich]

Run SQL is for action queries, what are you trying to do?

 

[LEXCERM]

Thanks Ken and Rich for replying.

I am trying to run a select query based on two fields on a form. I'm not sure if the BETWEEN syntax is correct. I have read that I think you need to enter the word PARAMETERS right at the beginning of the statement but don't know how.

Rgds,
Paul.

 

[KenHigg]

Paul, Rich has a good point, what are you trying to do with the query?

 

[LEXCERM]

Ken,

The form has a 'start date' and 'end date' fields. The query is run based on the data in these fields, hence the BETWEEN syntax.

Paul.

 

[KenHigg]

I understand why you need the date params but doing a DoCmd.RunSQL with that sql statement won't do anything. Do you want to use the records it returns for a report or to populate a form or what?

 

[LEXCERM]

Ken,

Sorry. To populate a form.

Paul.

 

[KenHigg]

Maybe:

mysql = "SELECT tbl_pallet_log.ID, tbl_pallet_log.Entry_Date, tbl_pallet_log.order_Number, tbl_pallet_log.Customer_No, tbl_pallet_log.Customer, tbl_pallet_log.Postcode, tbl_pallet_log.Pallets " & _
        "FROM tbl_pallet_log " & _
        "GROUP BY tbl_pallet_log.ID, tbl_pallet_log.Entry_Date, tbl_pallet_log.order_Number, tbl_pallet_log.Customer_No, tbl_pallet_log.Customer, tbl_pallet_log.Postcode, tbl_pallet_log.Pallets " & _
        "HAVING (((tbl_pallet_log.Entry_Date) Between #" & [Forms]![frm_customer_pallet_report]![txt_date_from] & "#  And #" & [Forms]![frm_customer_pallet_report]![txt_date_to] & "#));"

And use something like:

forms!myFormName.RecordSource = mysql

Instead of the docmd.runsql thing... (I think I have this correct )

 

[KenHigg]

Also... If you form your sql statements like the one below it may make debugging a little easier on the eyes :

mysql = "SELECT tbl_pallet_log.ID,  " & _
                         "tbl_pallet_log.Entry_Date,  " & _
                         "tbl_pallet_log.order_Number,  " & _
                         "tbl_pallet_log.Customer_No,  " & _
                         "tbl_pallet_log.Customer,  " & _
                         "tbl_pallet_log.Postcode,  " & _
                         "tbl_pallet_log.Pallets " & _
        "FROM tbl_pallet_log " & _
        "GROUP BY tbl_pallet_log.ID,  " & _
                         "tbl_pallet_log.Entry_Date,  " & _
                         "tbl_pallet_log.order_Number,  " & _
                         "tbl_pallet_log.Customer_No,  " & _
                         "tbl_pallet_log.Customer,  " & _
                         "tbl_pallet_log.Postcode,  " & _
                         "tbl_pallet_log.Pallets " & _
        "HAVING (((tbl_pallet_log.Entry_Date) Between #" & [Forms]![frm_customer_pallet_report]![txt_date_from] & "#  And #" & [Forms]![frm_customer_pallet_report]![txt_date_to] & "#));"

 

[LEXCERM]

Thanks very much Ken. I will try your suggestion tomorrow at work. And thanks for the sql writing tip.

Paul.

 

[GrahamT]

And use something like:

forms!myFormName.RecordSource = mysql

Instead of the docmd.runsql thing... (I think I have this correct )

I don't think this way of running the query works.

I would use the following code, which does work:

Dim qdf As QueryDef
Dim mysql as String

mysql = ". . . . . ." 'put your string here

Set qdf = CurrentDb.CreateQueryDef("", mysql)
qdf.Execute

 

[LEXCERM]

Thanks to everyone who has helped me on this. I am in my early stages of writing SQL and thanks for your patience. I have managed to get this working now but have a final issue that needs addressing.

The following code works correctly:-

    Dim DB As DAO.Database
    Dim Rc As DAO.Recordset
    Set DB = CurrentDb
    Set Rc = DB.OpenRecordset("SELECT tbl_stamp_numbers_receipts.Stamp_ID, " & _
                                "tbl_stamp_numbers_receipts.Date_Entered, " & _
                                "tbl_stamp_numbers_receipts.Stamp_No, " & _
                                "tbl_stamp_numbers_receipts.Category, " & _
                                "tbl_stamp_numbers_receipts.Rotation_Number, " & _
                                "tbl_stamp_numbers_receipts.Stamp_Status, " & _
                                "tbl_stamp_numbers_receipts.Updated " & _
                                "FROM tbl_stamp_numbers_receipts " & _
                                "WHERE (((tbl_stamp_numbers_receipts.Stamp_No) " & _
                                "Between '" & [Forms]![frm_Stamp No_Rotation Confirmation]![TXT_STAMP_FROM] & _
                                "' And '" & [Forms]![frm_Stamp No_Rotation Confirmation]![TXT_STAMP_TO] & "'));")

    Dim strcount
    While Not Rc.EOF And Not Rc.BOF
    strcount = strcount + 1
    Rc.MoveNext
    Wend
    MsgBox strcount
    
    Rc.Close
    DB.Close

However, when i add the following line I get a syntax error:-

'"' AND ((tbl_stamp_numbers_receipts.Stamp_Status)= 'NOT USED'));")

I think it is the criteria NOT USED that isn't syntaxed correctly.

Please advise and thanks again.

Paul.