Format date in VBA for SQL

Question

The blow code gives me this error massage

Syntax error in date in query expression (qryTrResult0.TrDate BETWEEN #01.01.2020# AND #01.11.2020)

I will be grateful if anybody help me.

Dim strSqlSelect As String
Dim DateFrom As Date
Dim DateTo As Date

DateFrom = InputBox("Please, enter the date from which you want to create the report.", "Sales report by day")
DateTo = InputBox("Now, enter the date until which you want to create the report.", "Sales report by day")

strSqlSelect = "SELECT qryTrResult0.InventoryNo, qryTrResult0.gname, qryTrResult0.Company, qryTrResult0.DosageForm, " & _
"qryTrResult0.Strength, Sum(qryTrResult0.SumOfQtypackage) AS SumOfQtypackage0, Sum(qryTrResult0.SumOfQtySheet) " & _
"AS SumOfQtySheet0, qryTrResult0.TrDate, Sum(qryTrResult0.PurSum) AS PurSum, Sum(qryTrResult0.SldSum) AS SldSum, " & _
"Sum(qryTrResult0.profit) AS profit, qryQtyOfShtsInPac.QtySheet, " & _
"((Sum([qryTrResult0]![SumOfQtypackage])*[QtySheet])+Sum([qryTrResult0]![SumOfQtySheet]))\[QtySheet] AS SumOfQtypackage, " & _
"((Sum([qryTrResult0]![SumOfQtypackage])*[QtySheet])+Sum([qryTrResult0]![SumOfQtySheet])) Mod [QtySheet] AS SumOfQtySheet, " & _
"qryTrResult0.Expense FROM qryTrResult0 INNER JOIN qryQtyOfShtsInPac ON qryTrResult0.PrID = qryQtyOfShtsInPac.PrID " & _
"WHERE qryTrResult0.TrDate BETWEEN #" & Format(DateFrom, "Short Date") & "# AND #" & Format(DateTo, "Short Date") & _
"# GROUP BY qryTrResult0.InventoryNo, qryTrResult0.gname, qryTrResult0.Company, qryTrResult0.DosageForm, qryTrResult0.Strength, " & _
" qryTrResult0.TrDate, qryQtyOfShtsInPac.QtySheet, qryTrResult0.Expense;"

Me.RecordSource = strSqlSelect

The users regional settings could mess this up. Better dropping controls on form (in place of input box) with date format set. This will allow users to enter dates in their own reginal format. However, you can't and don't want to use shortdate, since that will also produce a date format in their regional settings. ANY TIME you build a sql string date you MUST use ISO or USA date format. Answer below will solve this. However, since the input box allows free form text, you would do well using two text boxes set as date format to ensure the date input is in fact a date value as opposed to string. — Albert D. Kallal
– Albert D. Kallal, Commented Oct 11, 2020 at 16:44
You have several errors in this line: "WHERE qryTrResult0.TrDate BETWEEN #" & Format(DateFrom, "Short Date") & "# AND #" & Format(DateTo, "Short Date") & _ — Gi1ber7
– Gi1ber7, Commented Oct 15, 2020 at 14:08

Gustav · Accepted Answer · 2020-10-10 20:02:04Z

0

Try using the ISO sequence for the format:

"WHERE qryTrResult0.TrDate BETWEEN #" & Format(DateFrom, "yyyy\/mm\/dd") & "# AND #" & Format(DateTo, "yyyy\/mmm\/dd") & _

answered Oct 10, 2020 at 20:02

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Format date in VBA for SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related