using a form field in a query

[bravo6]

I have a query in which one of my fields is "Invoice date" from a table. As a criteria of that field I am using a date range from a form. The criteria expression is:

Between [forms]![YTD Query Calendar Prelim Results]![monthstart] And [forms]![YTD Query Calendar Prelim Results]![monthend]

The above works fine. So it seems to me that I should be able to add a field to the same query such as:

Date: [forms]![YTD Query Calendar Prelim Results]![monthstart]

As written above the field is looking for data in the same form where my criteria line got it from. For some reason, this field never populates (the results are blank in that field) I don't understand why.

 

[KenReay]

Hi

Form has to be open and the field must have a value, is it and has it?

Also

Date:, Date is a reserved Word, (eg Date Function Date()), so it is not wise to use it as a varaible name or an alias

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bravo6]

Ken, Thanks for your answer. Yes the form is open and the field is populated. Again, it's the same form/field that's being used as criteria elsewhere in another field of the same query. This really has me stumped.

D

 

[VRoscioli]

Have you tried changing the name of the alias, as Ken suggested?


-V

 

[bravo6]

Yes, that made no difference

 

[lespaul]

can you post the actual SQL?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[bravo6]

Sure, here is the SQL view. The problem area is:

[Forms]![YTD Query Calendar Prelim Results]![MonthStart] AS Start

Everything else is Ok.

SELECT DBA_Invoice.BkAgt, DBA_Invoice.InvoiceDate, DBA_Invoice.InvoiceNumber, DBA_Invoice.RevType, DBA_Invoice.TotalCost, AgentSign.[Agent Name], AgentSign.Team, [Forms]![YTD Query Calendar Prelim Results]![MonthStart] AS Start
FROM DBA_Invoice LEFT JOIN AgentSign ON DBA_Invoice.BkAgt = AgentSign.[Agent Sign]
WHERE (((DBA_Invoice.InvoiceDate) Between [forms]![YTD Query Calendar Prelim Results]![monthstart] And [forms]![YTD Query Calendar Prelim Results]![monthend]) AND ((DBA_Invoice.RevType)="a" Or (DBA_Invoice.RevType)="r" Or (DBA_Invoice.RevType)="f"));

 

[PHV]

I'd spell exactly the same the 2 instances of the start parameter ...

 

[bravo6]

I'm sorry I'm not following what you are saying.

 

[PHV]

[Forms]![YTD Query Calendar Prelim Results]![MonthStart]
is not spelled the same as
[forms]![YTD Query Calendar Prelim Results]![monthstart]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886