Parameter query driving me crazy (1 Viewer)

[danikuper]

Hi there!

I've tried everything and can't figure out what's happening...

I have a query that has a criteria based on two combo-boxes I have in my form. Although the combo-box works and the field names are all correct, the query messes up.

Here's what I have for my query criteria related to a field in my table called "month":

>=[forms]![frmWeekly_comp_report].[combostartmonth] And <=[forms]![frmWeekly_comp_report].[comboendmonth]

I want the query to get me all records within the chosen months. When running the query, the results don't show the months I've selected. If I have "1" in my combostartmonth and "2" in my comboendmonth, the query gets a bunch of months in between (3, 4, 5...). (my field "month" has number for the months, from 1 to 12).

Does anyone know why this might be happening?

Here's the SQL code if that helps...

SELECT DISTINCTROW [Weekly comparison by year].Year, Sum([Weekly comparison by year].AmountConverted) AS SumOfAmountConverted
FROM [Weekly comparison by year]
WHERE ((([Weekly comparison by year].Month)>=[forms]![frmWeekly_comp_report]![combostartmonth] And ([Weekly comparison by year].Month)<=[forms]![frmWeekly_comp_report]![comboendmonth]))
GROUP BY [Weekly comparison by year].Year;

Note: if I substitute in the query design or directly to the sql statement the combo boxes for the numbers, the query runs perfectly!

Thanks in advance... I'm really frustrated with this.

 

[Brianwarnock]

Hi there!

I've tried everything and can't figure out what's happening...

I have a query that has a criteria based on two combo-boxes I have in my form. Although the combo-box works and the field names are all correct, the query messes up.

Here's what I have for my query criteria related to a field in my table called "month":

>=[forms]![frmWeekly_comp_report].[combostartmonth] And <=[forms]![frmWeekly_comp_report].[comboendmonth]

I want the query to get me all records within the chosen months. When running the query, the results don't show the months I've selected. If I have "1" in my combostartmonth and "2" in my comboendmonth, the query gets a bunch of months in between (3, 4, 5...). (my field "month" has number for the months, from 1 to 12).

Does anyone know why this might be happening?

Here's the SQL code if that helps...

SELECT DISTINCTROW [Weekly comparison by year].Year, Sum([Weekly comparison by year].AmountConverted) AS SumOfAmountConverted
FROM [Weekly comparison by year]
WHERE ((([Weekly comparison by year].Month)>=[forms]![frmWeekly_comp_report]![combostartmonth] And ([Weekly comparison by year].Month)<=[forms]![frmWeekly_comp_report]![comboendmonth]))
GROUP BY [Weekly comparison by year].Year;

Note: if I substitute in the query design or directly to the sql statement the combo boxes for the numbers, the query runs perfectly!

Thanks in advance... I'm really frustrated with this.

Surely you cannot have = on both sides of the And ?

Brian

 

[danikuper]

Surely you cannot have = on both sides of the And ?

Brian

If that was the case, if I hard coded the numbers in place of the forms syntax (forms![...]) it wouldn't work... but it does!

daniel

 

[Pat Hartman]

I prefer Between for range checks since it reduces the length of the statement and is clearer. However, your syntax should work. Are you sure the form control references are valid? Is there a subform involved? Is the form that the fields are on open? It must be.

 

[danikuper]

I tried using Between as well but the result is the same...

Looking more into this, I discovered that when I enter "1" and "2" as parameters, the results are:
1
10
11
12
2

It seems that it's considering my entries as text and not as number. The field I'm querying is a datepart of a date field... could this be the problem? Datepart gives me a text result instead of a number?

thanks.

I prefer Between for range checks since it reduces the length of the statement and is clearer. However, your syntax should work. Are you sure the form control references are valid? Is there a subform involved? Is the form that the fields are on open? It must be.

 

[dcx693]

DatePart returns a Variant. Clearly, somewhere Access is looking at the numbers as if they are text. Why not query the date fields directly instead of querying on a DatePart calclulation? Use the Month function like:
Month([field]) Between [forms]![frmWeekly_comp_report].[combostartmonth] And [forms]![frmWeekly_comp_report].[comboendmonth]

 

[Pat Hartman]

Be careful when working with only part of a date field. You may need to consider year in the criteria.

 

[danikuper]

Year is being pulled from another combo box in my form. With that, however, I'm not having any problems...

Be careful when working with only part of a date field. You may need to consider year in the criteria.

 

[Brianwarnock]

Year is being pulled from another combo box in my form. With that, however, I'm not having any problems...

I think that Pat was referring to problems if the range crosses year ends.
Having read your posts again I see that it wasn't the months between 1 and 12( I thought it was a typing error in the 1st post, hence my rushed stupid answer, m'lud ) that were selected.

I cannot get this type of selection not to work using either YEAR and MONTH functions or Datepart and therefore wonder if we are getting the whole story here?

Pat I didn't understand the part about the Form not being open, in that case doesn't the query operate as a parameter query with the required data being requested?

 

[Brianwarnock]

Ok you haven't posted again but curiosity got the better of me and I've now got it to fail!
How? I left the format of the combo boxes blank, change it to fixed, I had done this automatically on my first test, and it should all be o.k. , of course you may have sorted it by now.

Brian

 

[danikuper]

Hi Brian,

I got sidetracked the past few days but am back to the database again. I did a workaround that consists of passing the value of the combo box to a text box on the same form and referencing that combo box in my query. With that it is now working... not sure why it would accept the text box value but not the combo box one... maybe the formatting does have something to do with it (?).

As I was on a deadline to get the report working, I had to go with the fastest way possible... I'll get back to it and see if now I can figure out exactly why that was happening.

Thanks for the help!
daniel

Ok you haven't posted again but curiosity got the better of me and I've now got it to fail!
How? I left the format of the combo boxes blank, change it to fixed, I had done this automatically on my first test, and it should all be o.k. , of course you may have sorted it by now.

Brian