Re: query from form

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: query from form
    ... The column type of is text. ... The query that fails refers to the text box on the form. ... Allen Browne - Microsoft MVP. ...
    (microsoft.public.access.queries)
  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • RE: Filtering a subform using many combo boxes
    ... I followed you instructions to the letter and every one of the criteria ... I described the typical properties of all my combo boxes in my original ... open the query "qryProjects" in design view. ... I have a main table named tblProjectData that contains all the data for each ...
    (microsoft.public.access.forms)
Loading