Prompts

[tucker61]

I have a button that runs 4 queries, Each of the 4 queries prompt for a start and end date, so i have tried to
convert the queries to code.

So far it has gone well, but i am struggling to see where the the prompt should sit in my code. I assume it is after the Between clause, but inspead of using the data from my original prompt, i am prompted again.

Please see enclosed.

Dim Startdate As Date
Dim Enddate As Date
Dim strsql As String

 
 Startdate = InputBox("Please Enter the Start Date")
                If Len(Startdate) < 1 Then
                Exit Function
                End If
                
 Enddate = InputBox("Please Enter the End Date")
                 If Len(Enddate) < 1 Then
                Exit Function
            End If
            WarningsOff
            
strsql = "SELECT Temp_Charges.Job_ID, Temp_Charges.Stock_Location, Temp_Charges.Delivery_Reference, Temp_Charges.Charge_Date, Temp_Charges.Raised_By, IIf([approved]=True,""Approved"",""Not Approved"") AS [Approved Y/N], Temp_Charges.Approved_By, Temp_Charges.APPROVED_TIME, Temp_Charges.Claim_Value, IIf([supplier_pays]=True,""Supplier"",""SDG"") AS [Supplier or SDG Charge?], Temp_Charges.Sup_Code, tblSuppliers.Sup_Desc, Temp_Charges.Comments, Temp_Charges.Contract_No, tblCalendar.Financial_Year, tblCalendar.WSSI_Period, tblCalendar.Financial_Week, Temp_Charges.Charge_Area, Tblqcsuppliersource.Origin, Temp_Charges.Passed_To_Finance AS Date_Passed_To_Finance " & vbCrLf & _
"FROM ((Temp_Charges LEFT JOIN tblCalendar ON Temp_Charges.Charge_Date = tblCalendar.Date) LEFT JOIN tblSuppliers ON Temp_Charges.Sup_code = tblSuppliers.Sup_Code) LEFT JOIN Tblqcsuppliersource ON Temp_Charges.Sup_code = Tblqcsuppliersource.Sup_Code " & vbCrLf & _
"WHERE (((Temp_Charges.Charge_Date) Between StartDate And EndDate) AND ((Temp_Charges.Claim_Value)>0) AND ((Temp_Charges.[Quote/Charge])=1)) " & vbCrLf & _
"ORDER BY Temp_Charges.Job_ID;"

 

[June7]

Concatenate variables. vbCrLf aren't really necessary.

strsql = "SELECT Temp_Charges.Job_ID, Temp_Charges.Stock_Location, Temp_Charges.Delivery_Reference, Temp_Charges.Charge_Date, Temp_Charges.Raised_By, IIf([approved]=True,""Approved"",""Not Approved"") AS [Approved Y/N], Temp_Charges.Approved_By, Temp_Charges.APPROVED_TIME, Temp_Charges.Claim_Value, IIf([supplier_pays]=True,""Supplier"",""SDG"") AS [Supplier or SDG Charge?], Temp_Charges.Sup_Code, tblSuppliers.Sup_Desc, Temp_Charges.Comments, Temp_Charges.Contract_No, tblCalendar.Financial_Year, tblCalendar.WSSI_Period, tblCalendar.Financial_Week, Temp_Charges.Charge_Area, Tblqcsuppliersource.Origin, Temp_Charges.Passed_To_Finance AS Date_Passed_To_Finance " & _
"FROM ((Temp_Charges LEFT JOIN tblCalendar ON Temp_Charges.Charge_Date = tblCalendar.Date) LEFT JOIN tblSuppliers ON Temp_Charges.Sup_code = tblSuppliers.Sup_Code) LEFT JOIN Tblqcsuppliersource ON Temp_Charges.Sup_code = Tblqcsuppliersource.Sup_Code " & _
"WHERE Temp_Charges.Charge_Date Between [COLOR=red]#" & StartDate & "# And #" & EndDate & "#[/COLOR] AND Temp_Charges.Claim_Value>0 AND Temp_Charges.[Quote/Charge]=1 " & _
"ORDER BY Temp_Charges.Job_ID;"

Better to have user input criteria in controls on form. It's easier to control and validate user input. Have code concatenate references to controls.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

How are you using this SQL?

 

[Minty]

Normally you would use a form to get the dates and then simply refer to the form in the query designer. In your date criteria (assuming your form was called frmReports) you would use

Between Forms!frmReports!StartDate And Forms!frmReports!EndDate

This saves you having to hand write the SQL each time.

In your example you would need to concatenate the variables into the string, and make sure they are in the correct format;

"WHERE (((Temp_Charges.Charge_Date) Between #" & Format(StartDate,"mm/dd/yyyy") &  " And #" &  Format(EndDate,"mm/dd/yyyy") &  "# ) .....rest of your code