SQL problem

[Vulcan1500]

In MS Access 2003 I have a form with a listbox and combobox. The listbox gives me records regarding expenses and with the combobox I can select a year. After selecting a year the listbox is filtered and is giving only the records for that year. So far things are doing as expected.

Now I want to print the filtered list. For that reason I built a query in SQL, but the Where clause is my problem.

In the query I have a column Year: Format([Date],'yyyy') and the Where clause is WHERE "QueryName.Year = '" & Me.ComboboxName & "'". After running this the database is asking for a value for QueryName.Year.

If I add e.g. 2008 it gives me all records. When I run the query only and add 2008 to that column as a filter it is giving me the correct results.

 

[abhi.karnawat]

hi
i cant really understand what u want to do as what i read from the post it is working as u want .. like when u put in the year it gives u the filtered result.. so what seems to be the problem.. is the the printing?
regards
abhi

 

[JPaulo]

try;

SELECT YouTable.Field1, YouTable.Field2
FROM YouTable
WHERE (((Format([FieldDate],"yyyy"))=[Forms]![YouForm]![ComboboxName]))
GROUP BY YouTable.Field1, YouTable.Field2;


Please note that the word Year' and a reserved word in Access.

 

[Vulcan1500]

Reading again what I wrote I understand it's not very clear. Selecting a year with the combobox the listbox is filtered correctly. Then, by pushing a commandbutton, a report is made and displayed on screen. This report is not correct as soon as the combobox is used to filter the data for one year. The command I use is:

Dim strDocName As String
Dim strWhere As String
strDocName = "rptBTWOverzichtKosten"
If cmdGevorderdOp.Enabled = True Then
strWhere = "qryBTWOverzichtKosten.DatumBTWGevorderd " & "Is Null"
Else
If Me.cboJaar = "" Then
strWhere = "''"
Else
strWhere = "qryBTWOverzichtKostenAfdruk.Jaar = '" & Me.cboJaar & "'"
End If
End If
DoCmd.OpenReport strDocName, acPreview, , strWhere

 

[Vulcan1500]

It works when strWhere becomes:
"qryBTWOverzichtKosten.DatumBTWGevorderd " & "Is Null" or "''"

As soon as cboJaar is used strWhere becomes:
"Format([Datum];'yyyy') = '" & Me.cboJaar & "'" and is giving an error

Maybe I'm mixing up " and '. Not sure when to use what!

 

[namliam]

"Format([Datum];'yyyy') = '" & Me.cboJaar & "'"

Try replacing the ; by ,

You use " to denote the start and end of a text string in VBA
You use ' to denote the string in the where clause, instead of the ' you can also use "" to escape the "...

"Format([Datum],""yyyy"") = """ & Me.cboJaar & """"

Which in the actual SQL will endup being:
Format([Datum],"yyyy") = "2009"

Make sure your field is actually called Datum too, and that this datum field is in the query.

Met vriendelijke groeten uit amsterdam / Greets from amsterdam

 

[Vulcan1500]

Thanks Namliam! It works. Great!

I would like to understand it, but I have no idea which quotes are pairs. Can you help with this?

If I add a second combo can I join them in a strWhere with an And-function? Thinking of something like:

strWhere = "AND(Format([Datum],""yyyy"") = """ & Me.cboJaar & ""","Format([Datum],""q"") = """ & Me.cboKwartaal & """)"

Thanks for your time!

 

[namliam]

No you are thinking excel like and function
SQL where goes
Where onething = Another
And thatThing = thisting
and Here = there

" is pared with another "
' is pared with another '

Within a normal string, like so
Dim X as string
X = "this is some text"

You cannot use a simple " like so
X = "this is "some" text"
Because that would denote the end of the string, if you want to use the " you have to "escape" it by doubling it up...
X = "this is ""some"" text"

Or use the "other" ' to solve your issue
X = "this is 'some' text"

I hope that is clear enough?

 

[Vulcan1500]

Clear and with this I will try to solve the issue with two comboboxes. Thanks again Mailman!