query between 2 dates

[navin]

Hi,

Please tell me what's the mistake in the below query.

TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN Forms!frmReport!txtFromDate AND Forms!
frmReport!txtToDate AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus;

Thanks for the help.

Navin

 

[Jeff Boyce]

How do you know there's a mistake?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[navin]

When i run the query it gives error " the microsoft jet database
engine does not recognise 'Forms!frmReport!txtFromDate' as a valid
field name or expression"

How do you know there's a mistake?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

Please tell me what's the mistake in the below query.

TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN Forms!frmReport!txtFromDate AND Forms!
frmReport!txtToDate AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus;

Thanks for the help.

Navin

 

[Roger Converse]

Hello,

There are a couple of things that you'll probably need. I am assuming your
dates are date variables.

strsql = "TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN #" & Forms!frmReport!txtFromDate & "#AND #" & Forms!
frmReport!txtToDate & "#AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus; "

Based on the error you're receiving, I would also double check the form and
control variables and make sure nothing is miskeyed.

HTH
Roger

When i run the query it gives error " the microsoft jet database
engine does not recognise 'Forms!frmReport!txtFromDate' as a valid
field name or expression"

How do you know there's a mistake?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

Please tell me what's the mistake in the below query.

TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN Forms!frmReport!txtFromDate AND Forms!
frmReport!txtToDate AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus;

Thanks for the help.

Navin

 

[navin]

thanks for the reply. but i am not running this query in VBA. i am
trying to save this query in access and then create a report based on
this query.

Hello,

There are a couple of things that you'll probably need. I am assuming your
dates are date variables.

strsql = "TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN #" & Forms!frmReport!txtFromDate & "#AND #" & Forms!
frmReport!txtToDate & "#AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus; "

Based on the error you're receiving, I would also double check the form and
control variables and make sure nothing is miskeyed.

HTH
Roger

When i run the query it gives error " the microsoft jet database
engine does not recognise 'Forms!frmReport!txtFromDate' as a valid
field name or expression"

How do you know there's a mistake?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

Please tell me what's the mistake in the below query.

TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN Forms!frmReport!txtFromDate AND Forms!
frmReport!txtToDate AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus;

Thanks for the help.

Navin

 

[Roger Converse]

When you go to open or create the report is the form with the dates open? I
think it needs to be.

The fact that you are setting up this query as opposed to running it in VBA
shouldn't matter. You still need the "#" around the dates. I am not sure
that the query builder is smart enough to put them in when pulling from a
form.

Thanks,
Roger

thanks for the reply. but i am not running this query in VBA. i am
trying to save this query in access and then create a report based on
this query.

Hello,

There are a couple of things that you'll probably need. I am assuming your
dates are date variables.

strsql = "TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN #" & Forms!frmReport!txtFromDate & "#AND #" & Forms!
frmReport!txtToDate & "#AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus; "

Based on the error you're receiving, I would also double check the form and
control variables and make sure nothing is miskeyed.

HTH
Roger

When i run the query it gives error " the microsoft jet database
engine does not recognise 'Forms!frmReport!txtFromDate' as a valid
field name or expression"


Jeff Boyce wrote:
How do you know there's a mistake?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

Please tell me what's the mistake in the below query.

TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN Forms!frmReport!txtFromDate AND Forms!
frmReport!txtToDate AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus;

Thanks for the help.

Navin

 

[Jeff Boyce]

As Roger points out, when you tell Access to refer to a form's control in a
query, the form must be open. Otherwise, Access can't "see" it (...
besides, if the form isn't open, the control doesn't have a value in it,
right?!).

Regards

Jeff Boyce
Microsoft Office/Access MVP

When i run the query it gives error " the microsoft jet database
engine does not recognise 'Forms!frmReport!txtFromDate' as a valid
field name or expression"

How do you know there's a mistake?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

Please tell me what's the mistake in the below query.

TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN Forms!frmReport!txtFromDate AND Forms!
frmReport!txtToDate AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus;

Thanks for the help.

Navin

 

[navin]

Thanks. its working now. i used the query in VBA with the help of
querydef. However, my another question is, can a query be deleted from
the database through VBA.

i am using CurrentDb.CreateQueryDef "qryPOTypeByDate", sqlInsert, to
create the query in the database. If the query name does not exist
already then it runs fine but at second time, it gives me error saying
the query name already exist.

So i need to remove the query from the database, somthing like how we
drop a table, i need to drop a query, is it possible?

thanks again for your help.

navin

As Roger points out, when you tell Access to refer to a form's control in a
query, the form must be open. Otherwise, Access can't "see" it (...
besides, if the form isn't open, the control doesn't have a value in it,
right?!).

Regards

Jeff Boyce
Microsoft Office/Access MVP

When i run the query it gives error " the microsoft jet database
engine does not recognise 'Forms!frmReport!txtFromDate' as a valid
field name or expression"

How do you know there's a mistake?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

Please tell me what's the mistake in the below query.

TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN Forms!frmReport!txtFromDate AND Forms!
frmReport!txtToDate AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus;

Thanks for the help.

Navin

 

[Krzysztof Pozorek [MVP]]

(...)

When i run the query it gives error " the microsoft jet database
engine does not recognise 'Forms!frmReport!txtFromDate' as a valid
field name or expression"

1. You need add parameters section,
2. or write Eval("Forms!frmReport!txtFromDate") rather than
Forms!frmReport!txtFromDate.

ad. 1) Expressions like Forms!frmReport!txtFromDate don't work in crosstab
query, until You will add parameters section. You need write something like
this:

PARAMETERS Forms!frmReport!txtFromDate DateTime,
Forms!frmReport!txtToDate;

TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq]
FROM tblPOTracker
WHERE transDate BETWEEN Forms!frmReport!txtFromDate AND Forms!
frmReport!txtToDate AND tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus;

ad. 2) In second solution, You write:

TRANSFORM Count(tblPOTracker.buyerReq) AS CountOfbuyerReq
SELECT tblPOTracker.transDate AS TransactionDate,
tblPOTracker.buyerName AS Buyer, Count(tblPOTracker.buyerReq) AS
[Total Of buyerReq] FROM tblPOTracker
WHERE transDate BETWEEN Eval("Forms!frmReport!txtFromDate")
AND Eval("Forms!frmReport!txtToDate") AND
tblPOTracker.requestStatus='Standard' OR
tblPOTracker.requestStatus='HOT'
GROUP BY tblPOTracker.transDate, tblPOTracker.buyerName
PIVOT tblPOTracker.requestStatus;

Kris, MVP, Poland
www.access.vis.pl

 

[Krzysztof Pozorek [MVP]]

(...)

PARAMETERS Forms!frmReport!txtFromDate DateTime,
Forms!frmReport!txtToDate;

I miss one DateTime... Right form is such:
PARAMETERS Forms!frmReport!txtFromDate DateTime,
Forms!frmReport!txtToDate DateTime;

K.P

 

[navin]

Thank you very much. Worked perfectly.

NG

(...)

I miss one DateTime... Right form is such:
PARAMETERS Forms!frmReport!txtFromDate DateTime,
Forms!frmReport!txtToDate DateTime;

K.P