Re: query from form

So you verified that the control name was spelled correctly.
And you ran the query directly (not via the report), after moving the focus
on the form to another control (e.g. by tabbing out of the text box.)

I don't know what else to suggest. Guess you could try declaring the
parameter (Parameters on Query menu, in query design view), though I don't
see that it would solve the problem, and it might introduce another one:
http://allenbrowne.com/bug-13.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"geebee" <geebee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FAC097A4-0825-41B9-A405-AA2287C64821@xxxxxxxxxxxxxxxx
The column type of [Status] is text. This column is indexed (duplicates
OK).
And the focu moves to another field within the form before the report is
generated. I tested this by running the query with the form open, after
renaming the [delinquent_criteria] to [delinquentcriteria], and I am
getting
a "The Microsoft Jet database engine does not recognize
'[Forms]![frm_criteria]![delinquentcriteria]' as a valid field name or
expression." error message.



"Allen Browne" wrote:

The query that works has the literal value as criteria.
The query that fails refers to the text box on the form.

Presumably the form is open, and you typed the value pdb1-30 into the
text
box. Any chance the focus is still in that text box? If so, the Value
will
not have been accepted yet, and so the query will not work. Move the
focus
out of the text box to another control on the form, and see if that makes
a
difference.

If it still fails, JET does have a problem with fields that contain a
dash
character. They are handled inconsistently depending on whether the field
is
indexed or not. What is the data type of the Status field? And is it
indexed? More info on this issue:
http://support.microsoft.com/kb/271661/en-us

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"geebee" <geebee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F8D6C183-CD3C-4E93-9091-A81E689CB731@xxxxxxxxxxxxxxxx

I have the following:
TRANSFORM Count(Tbl_masterpop.[Loan Acct #]) AS [CountOfLoan Acct
#]SELECT
Tbl_masterpop.status FROM Tbl_masterpop WHERE (([popenterdt] between
#7/1/2006# AND #7/31/2006#) AND
(([Status]=[Forms]![frm_criteria]![delinquent_criteria]))) GROUP BY
Tbl_masterpop.status ORDER BY Format([popenterdt],'m/dd')PIVOT
Format([popenterdt],'m/dd');

but it returns no results. However, when I change the query to:
TRANSFORM Count(Tbl_masterpop.[Loan Acct #]) AS [CountOfLoan Acct #]
SELECT Tbl_masterpop.Status
FROM Tbl_masterpop
WHERE (((Tbl_masterpop.PopEnterDt) Between #7/1/2006# And #7/31/2006#)
AND
((Tbl_masterpop.Status)="pdb1-30"))
GROUP BY Tbl_masterpop.Status
ORDER BY Format([popenterdt],'m/dd')
PIVOT Format([popenterdt],'m/dd');

It works just fine.

Can someone tell me how I can get the first version to work?

Thanks in advance,
geebee





.

Relevant Pages

  • RE: Why cant I open a report using VB?
    ... You'll also get a message box telling you the value of the txtID control - ... Run the query to see if it returns any records. ... know that the query is fine, and so is the data that the report is based on. ... intTestID = Forms!frmDrive!Text35.Value ...
    (microsoft.public.access.modulesdaovba)
  • RE: Why cant I open a report using VB?
    ... Right, plugging the number into the criteria works, so the query itself is ... "Stuart At Work" wrote: ... know that the query is fine, and so is the data that the report is based on. ... is to check that the control that you refer to to populate the variable ...
    (microsoft.public.access.modulesdaovba)
  • RE: Filter Query by Year on a form
    ... If the check box does not affect what data are retrieved by the query, ... launch several different reports. ... it will launch that particular report say material cost report or ... If the user enters a year in the from year control and nothing in the to ...
    (microsoft.public.access.queries)
  • Re: Change of field name causes application to crash
    ... I can create a simple Sum query and get the same crash. ... UNION ALL query to simply UNION. ... > are part of the report. ... Sometimes when you have a control ...
    (microsoft.public.access.modulesdaovba)
  • RE: Report weirdness....
    ... My query works fine, Joseph - it's the report based on the query I'm getting ... I've tried removing JUST the control involved, & the report works fine. ... "Duane Hookom" wrote: ...
    (microsoft.public.access.reports)