applying a criteria in the button that opens a report

[prmitchell]

I have the onlick of a button programmed with

Dim stDocName As String
Dim stCriteria As String
stDocName = "Rallies this year"
stCriteria = "[Year of Rally] = " & Year(Date)
MsgBox (stCriteria)
DoCmd.OpenReport stDocName, acViewPreview, "", "", stCriteria

the output in the msgbox is [Year of Rally] = 2013 which seems fine
but the OpenReport command then gives me a type mismatch error

the [Year of Rally] is a calculated field in the query - the record source.
I have tried
stCriteria = "[queryname].[Year of Rally] = " & Year(Date)
and same problem

suggestions please.

Peter

 

[pbaldy]

Sounds like the field may be text:

http://www.baldyweb.com/wherecondition.htm

 

[prmitchell]

Thanks Paul for the reply
certainly sounds like a text vs numeric problem
I have tried
stCriteria = "[Year of Rally] = '" & Year(Date) & "'"
and this makes the msgbox output [Year of Rally] = '2013'
but I still get the type mismatch problem

the [Year of Rally] calculated field in the query/recordsource produces data that is right aligned - which indicates not text

=> still a question

Peter

 

[Mihail]

Post the query SQL

 

[prmitchell]

Thanks for the reply
Here first is the straight SQL

SELECT Rallies.Name, Races.Date, Owners.Surname, Cars.[Registration Number], Year([Date]) AS [Year of Rally]
FROM Rallies, Races, Entries, Cars, Owners
WHERE Owners.[Licence #] = Cars.[Licence #]
AND
Cars.[Registration Number] = Entries.[Registration Number]
AND
Rallies.[Rally ID] = Races.[Rally ID]
AND
Races.[Race Code] = Entries.[Race Code]
AND
(((Year([Race Date]))=Year(Date())));

but instead of having the last line in the query I am trying the write the criteria into the button that opens the report which I have done before with strings of text - but not equal to the year of date - which is numeric.


here is the Access SQL is

SELECT Rallies.Name, Races.Date, Owners.Surname, Cars.[Registration Number], Year([Date]) AS [Year of Rally]
FROM Owners INNER JOIN (Rallies INNER JOIN (Races INNER JOIN (Cars INNER JOIN Entries ON Cars.[Registration Number] = Entries.[Registration Number]) ON Races.[Race Code] = Entries.[Race Code]) ON Rallies.[Rally ID] = Races.[Rally ID]) ON Owners.[Licence #] = Cars.[Licence #]
WHERE (((Year([Race Date]))=Year(Date())));

 

[Mihail]

From what I see, you have a field named "Date" in the table "Races".
And, of course, a control with the same name in the form.
Bad. Very bad. The "Date" is a reserved word for Access and should never be used as field (or control name).

On the other hand, the calculated field Yer Of Rally (another rule: do not use spaces in field names) seems to not be necessary.

Sorry, but is too hard for me to debug this.
I'm pretty sure that, if you will upload the database, I can help you.

 

[Mihail]

Access 2007

 

[JHB]

here is the Access SQL is

SELECT Rallies.Name, Races.Date, Owners.Surname, Cars.[Registration Number], Year([Date]) AS [Year of Rally]
FROM Owners INNER JOIN (Rallies INNER JOIN (Races INNER JOIN (Cars INNER JOIN Entries ON Cars.[Registration Number] = Entries.[Registration Number]) ON Races.[Race Code] = Entries.[Race Code]) ON Rallies.[Rally ID] = Races.[Rally ID]) ON Owners.[Licence #] = Cars.[Licence #]
WHERE (((Year([Race Date]))=Year(Date())));

From where comes the field [Race Date], and is [Race Date] a date field type?

 

[prmitchell]

I have uploaded the zip file
I have changed the name of the name of the field so it's not just Date

The button on the form produces the type mismatch error running a query without a criteria.

Trust I haven't made any mistakes in deleting other components and code.

 

[prmitchell]

In reply to JHB, I should have had Races.[Race Date] in the select line and this field is Date data type.

 

[JHB]

The correct line for open the report is, (you have a "," to much in so you are trying to set the Window mode":

DoCmd.OpenReport stDocName, acViewPreview, , stCriteria

 

[prmitchell]

wonderful John (I think John)
yes one extra parameter. thank you indeed

 

[JHB]

wonderful John (I think John)

No - Jørn (I'm not sure you can see the second letter, therefore - Joern.)