Get query Criteria from a combo box

[Eljefegeneo]

I am trying to get a list of birthdays for each the current month and the next month. I have a form to select either the criteria for the current month or the next month using a combo box, but when I try to pass this to the parameter query, I get nothing.
Query:

  SELECT tblMain.ClientID, ([Title]) & (" "+[FirstName]) & (" "+[MiddleName]) & (" "+[Surname]) & (" "+[NameSuffix]) AS Client, tblMain.ProgramTitle, tblMain.Birthday
  FROM tblMain INNER JOIN qryOnAirActive ON tblMain.ClientID = qryOnAirActive.ClientID
  WHERE ((Not (tblMain.Birthday) Is Null) AND ((Month([Birthday]))=[Forms]![frmBirthdaySelect]![Combo0]) AND ((qryOnAirActive.CustomerStatus)="Active"))
  ORDER BY tblMain.Birthday;

The two parameters are:
Month(Now))
Month(Now())+1

If I paste either of these into the query, the desired result is achieved. But when I run it using the criteria from the combo box, I get nothing but a blank query.
How do I get this to work?

 

[GinaWhipp]

Is it a single column Combo Box?

Side note, I'd use Date() as opposed to Now().

 

[Eljefegeneo]

single column

 

[GinaWhipp]

Then it's might be because it's a Combo Box which makes the Date format as Text instead of a Date. What happens if you replace the Combo Box with a Text Box?

 

[Eljefegeneo]

The same thing. I did try that as my standard backup when I couldn't get a combo box to work in the past. But it still won't pass the criteria to the parameter.

 

[GinaWhipp]

How is the Combo Box formatted? As a Date/Time field?

 

[Eljefegeneo]

I tired to re-set the combo box format as a date, but when I enter the two variables, it says I cannot select either. Wrong type of data.

 

[GinaWhipp]

What is the Data Type of the Birthday? Try formatting that is a Number then they should match.

 

[Eljefegeneo]

The data type of the birthday is short date. I don't understand what you sure saying. Change it to a number?

 

[GinaWhipp]

No, what I'm thinking is if you just pulling the month then surround the Combo Box in Val()...

Val([Forms]![frmBirthdaySelect]![Combo0])

 

[Eljefegeneo]

I do appreciate your help, but still not result. I did see what value that would pass and it was 0 in both cases. I did this by adding a text box and passing the value to it. If I set is as date, it comes up as 12/30/1899 or when I click on the text box it comes up as 12:00:00 Am regardless if I use Now or Date.

 

[GinaWhipp]

Hmm, okay then something else is going on. Can you upload a file with dummy data?

 

[Eljefegeneo]

Thank you and yes I will but it won't be until tomorrow.

 

[GinaWhipp]

No problem... I'll be around

 

[Eljefegeneo]

Found a few minutes tonight, see the attached.

 

[GinaWhipp]

Well, found the problem right away. You can't use...

Month(Now))
Month(Now())+1

As criteria, you have to actually show the Month number and in the query you have to reference the Combo Box. I just uploaded a fixed one.

 

[arnelgp]

also this one

 

[Eljefegeneo]

Thank to both of you. I think I understand how it works, but, a big but here, why do my two queries get the correct result when I use the either of the criteria in separate queris but not when I pass the parameters from a combo box?

Month(Now))
Month(Now())+1

 

[arnelgp]

on your first code you are lacking on closing parenthesis.

you may use Eval() instead on your query, this will evaluate your string expression and return the Month as integer.

Expr: Eval([Forms]!yourForm!cboName)

 

[GinaWhipp]

Hmm, thinking how to explain... Let's try:

Month(Now()) returns 2 (for February), Month being the Function and Now() being the day. However, in the Combo Box, as part of the selection it returns nothing it is literally "Month(Now())" as it's not a Function just a bunch of characters when used as a value in a Combo Box.

Make sense?