Text box references not working.

[Greyowlsl]

Hi,

I have a query which has 3 'where' fields, a 'group by' field and a 'count' field. The 'where' fields each have a criteria that refer to a form ([Forms]![Forecast_Update]![text17]), on the form is a button that runs the query (once the text boxes are filled), the query however shows up nothing, it only displays the 2 headings.

If i remove the form references and put in the criteria manually, then it works. 1 of the form references display "Between #1/01/2012# and #31/01/2012#", is this a problem?

Thanks,
Leon

 

[John Big Booty]

Is the query running on an existing record or an a new record?

 

[Greyowlsl]

Its working on existing records

 

[boblarson]

Post the SQL of the query when you use the form references and there are no records returned. It would be helpful if we can see that SQL because it is likely not correct.

 

[John Big Booty]

So you only get the error on new records? If so this would suggest that the record has not yet been committed to the table. So prior to running the query try setting the Form's Dirty property to False

 

[Greyowlsl]

No i meant 'it uses existing records', sorry.

This is the SQL:

SELECT DateSerial(Year([REPAIRS].[REPAIR_DATE_SHIPPED]),Month([REPAIRS].[REPAIR_DATE_SHIPPED]),1) AS Month, Count([1500].[SERIAL NO]) AS [CountOfSERIAL NO]
FROM 1500 INNER JOIN REPAIRS ON [1500].[SERIAL NO] = REPAIRS.[SERIAL NO]
WHERE ((([1500].[PRODUCT ID])=[Forms]![Forecast_Update]![List11]) AND (([1500].[DATE SENT])=[Forms]![Forecast_Update]![text17]) AND (([1500].STATE)=[Forms]![Forecast_Update]![List21]) AND ((REPAIRS.FAULT_DC_BREAKER)<>0) AND ((REPAIRS.REPAIR_DATE_SHIPPED)>#12/31/2011#))
GROUP BY DateSerial(Year([REPAIRS].[REPAIR_DATE_SHIPPED]),Month([REPAIRS].[REPAIR_DATE_SHIPPED]),1);

 

[boblarson]

If i remove the form references and put in the criteria manually, then it works. 1 of the form references display "Between #1/01/2012# and #31/01/2012#", is this a problem?

I just realized what you showed.

Two things wrong with your form reference showing

Between #1/01/2012# and #31/01/2012#"

First off, a text box can't have that in it and be criteria. Access evaluates that as a literal string. You can have SET of Text boxes on your form, one for the beginning date and one for the ending date and then you can use the criteria in SQL by writing the BETWEEN ... AND parts and referencing the form so like

[FieldName] Between [Forms]![FormNameHere]![StartDate] AND [Forms]![FormNameHere]![EndDate]

But you can't have the single text box with the whole thing. It doesn't work.

Second, you need to format your date inputs into a query in U.S. Date Format or a non-ambiguous format. See here for more about that:
http://allenbrowne.com/ser-36.html

 

[vbaInet]

First off, a text box can't have that in it and be criteria.

I actually did think this was what he was doing in his first post but I just thought I should wait and see how the thread progresses.

By the way Greyowlsl, 1500 is a rather uncanny name for a table or a query. Care to tell us why this is so or what it represents?

 

[Greyowlsl]

Hi guys, thanks for the help. The issue i thinkwas that i didn't know that the '#' characters were entered automatically... so i had it in the text boxes (ie literal string), so now i just simply have the date in the text boxes and 'Between [textX] and [textY]' and it now works fine.

vbaInet,
I'm actually not quite sure what that name represents, the table and database was made many years ago... probably why we still use access97.

 

[vbaInet]

Hi guys, thanks for the help. The issue i thinkwas that i didn't know that the '#' characters were entered automatically... so i had it in the text boxes (ie literal string), so now i just simply have the date in the text boxes and 'Between [textX] and [textY]' and it now works fine.

Pretty much what Bob explained in his last post.

vbaInet,
I'm actually not quite sure what that name represents, the table and database was made many years ago... probably why we still use access97.

Alright. Best to leave it as it is then.

 

[Greyowlsl]

Pretty much what Bob explained in his last post.

Aye, didn't mean to brand it as my own idea/solution, Bob was thanked.

...Thanks Bob

 

[vbaInet]

I know Greyowlsl, I was only making reference to the complete explanation.