When using a Form field as a criteria in a query, It works in one query but not in aoother
When using a Form field as a criteria in a query, It works in one query but not in aoother
A PDF of form is not useful to analyze issue. Provide queries and sample data. If you want to provide db, follow instructions at bottom of my post.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Is the Form referenced (TrackNestlings) open when you run the Query...it needs to be or you'll get this error!And I'm not aware of any way to "run the form TrackNestlings with the parameters below" from a graphic displayed in a PDF file...is this something new?
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
Another possibility is your combo box is not named Bird.
I am attaching the database to be used.
I can't open the file right now, on a mobile device. Is there a crosstab query involved? There appeared to be, and if so see post 4.
Advise not to use spaces in naming convention. So Start Date and End Date should be StartDate and EndDate.
The CROSSTAB opens on its own. The SELECT with criteria opens on its own. But the query that joins them fails. Will need to use PARAMETERS clause in the SELECT query.
The data range criteria is not set up yet. Date/time field has time component. I expect you will get inconsistent results unless you account for the time component.
Between [Forms]![TrackNestlings]![StartDate] And [Forms]![TrackNestlings]![EndDate] & " 23:59:59"
If there are multiple records for different times in same day, the CROSSTAB will summarize for each date and time - is that what you want? Or should summarize by date?
PARAMETERS Forms!TrackNestlings!Bird Text ( 255 ), Forms!TrackNestlings!StartDate DateTime, Forms!TrackNestlings!EndDate DateTime;
There is no need to actually open the two separate queries, just open the last one.
Instead of opening queries, really should build a report. Users should not interact with tables and queries. Although, building a stable report based on CROSSTAB can be tricky.
Why does it matter whether there are photos or comments when selecting records?
Why even use Temp table?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Thanks. Using the PARAMETER clause resolved the problem.Advise not to use spaces in naming convention. So Start Date and End Date should be StartDate and EndDate.
The CROSSTAB opens on its own. The SELECT with criteria opens on its own. But the query that joins them fails. Will need to use PARAMETERS clause in the SELECT query.
The data range criteria is not set up yet. Date/time field has time component. I expect you will get inconsistent results unless you account for the time component.
Between [Forms]![TrackNestlings]![StartDate] And [Forms]![TrackNestlings]![EndDate] & " 23:59:59"
If there are multiple records for different times in same day, the CROSSTAB will summarize for each date and time - is that what you want? Or should summarize by date?
PARAMETERS Forms!TrackNestlings!Bird Text ( 255 ), Forms!TrackNestlings!StartDate DateTime, Forms!TrackNestlings!EndDate DateTime;
There is no need to actually open the two separate queries, just open the last one.
Instead of opening queries, really should build a report. Users should not interact with tables and queries. Although, building a stable report based on CROSSTAB can be tricky.
Why does it matter whether there are photos or comments when selecting records?
Why even use Temp table?
I had to use Temp table because I was getting the same error when I was creating a Crosstab query using the query. I don't have to use the table if I add PARAMETER clause in that query as well.
I want to have one row for every Date & Time that is why I have included Date and time in the same field