Vba string

[rchtan]

Hi



Fairly new to using vba in access. The coding was workng fine until i decided to add in a new form text field where user can input a date and return a query where it list all items on and before. The code in red are my new addition.


strSQL = "SELECT qryprintlist.* FROM qryprintlist " & "WHERE qryprintlist.[item_Description] " & stritemStatus & strStatusCondition & "qryprintlist.[Category_Status] " & strcategoryStatus & strOrigCondition & "qryprintlist.[VendorID] " & strOriginator & strvendorCondition & "qryprintlist.[Area_ID] " & strAreaName & " AND " & "qryprintlist.[Date_Initiated] & " <= " & #" & txtDateTo.Value & "# ";"

Thanks

[June7]

I don't understand the query. All those concatenated fields and variables in the WHERE clause don't have a parameter. Some have space separators and some don't. The sql statement was incomplete before the change. I don't see how it was working. Too much concatenation in the new part and missing a quote mark, simplify.

" AND qryprintlist.[Date_Initiated] <= #" & txtDateTo.Value & "#;"

[rchtan]

Thanks June7 for your ealier reply. I went back and rework on it
strSql = "qryprintlist.[item_Description] " & stritemStatus & strOrigCondition & "qryprintlist.[Category_Status] " & strCategory & strcatCondition & "qryprintlist.[VendorID] " & strvendorName & " AND qryprintlist.[itemDate] Between #" & Format(txtDateFrom.value, "dd-mmm-yyyy") & "#" & " AND #" & Format(txtDateTo.value, "dd-mmm-yyyy") & "#".
How do i get null date into this extract? I tried adding OR WHERE "qryprintlist.[itemDate]" is null.
Sorry my understand of VBA is really basic

Thanks

[June7]

It isn't really VBA you are struggling with here. It is the syntax of SQL statements and how to build them in VBA. The SQL statement still doesn't make sense. Need some operators (=, <=, >=, <, >, <>) in there somewhere. Would VendorID = strVendorName?

Parameters for text fields need apostrophe delimiters.

Maybe:

strSql = "qryprintlist.[item_Description] ='" & stritemStatus & strOrigCondition & "' AND qryprintlist.[Category_Status] ='" & strCategory & strcatCondition & "' qryprintlist.[VendorID] ='" & strvendorName & "' AND qryprintlist.[itemDate] Between #" & Me.txtDateFrom & "# AND #" & Me.txtDateTo & "# AND qryprintlist.[ItemDate] Is Null"

[rchtan]

Thanks for your quick reply. I will work on the statemet.
Hope you dont mind if i have another question. I have created a union between 2 crosstab queries
Below is how the result look like
Area_ID ItemID Town1Cost Town2Cost Town1Qty Town2Qty
-----------------------------------------------------------------
6036 Sugar 50 60 0 0
6036 Sugar 0 0 25 30
6036 Salt 80 120 0 0
6036 Salt 0 0 20 30

What do i need in the sql query to get sum that i dont need another query to do that?

Area_ID ItemID Town1Cost Town2Cost Town1Qty Town2Qty
-----------------------------------------------------------------
6036 Sugar 50 60 25 30
6036 Salt 80 120 20 30

[June7]

Sorry, you need another query.

Do a GROUP BY (totals) query with the UNION as the data source. Or use the UNION as the RecordSource for a report that uses Grouping & Sorting with summary calcs.