PARAMATER QUERY USING FIELD

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

query creteria:

Between Date()-"X" and Date()

I want to create a parameter query for the "-x" portion of these creteria.
But later on, in a report I, want to show the value of "X", example:

This report is valid for the last "X" days.
 
criteria -->
Between Date()-[Enter Number of Days:] And Date()

If you are planning to create a report, it would be best to
use a form to collect the information

create a blank form
Name it --> ReportMenu

create a textbox field with no Controlsource
Name--> NumDays

in your query
Between Date()-forms!ReportMenu!NumDays And Date()


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
I haven't used a form to do a query before. I always used the query in design
view. None of the data that I have is manually entered. It comes from
outside/linked data bases. I simply query the data through access. Does this
affect your suggestion?
strive4peace said:
criteria -->
Between Date()-[Enter Number of Days:] And Date()

If you are planning to create a report, it would be best to
use a form to collect the information

create a blank form
Name it --> ReportMenu

create a textbox field with no Controlsource
Name--> NumDays

in your query
Between Date()-forms!ReportMenu!NumDays And Date()


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



George said:
query creteria:

Between Date()-"X" and Date()

I want to create a parameter query for the "-x" portion of these creteria.
But later on, in a report I, want to show the value of "X", example:

This report is valid for the last "X" days.
Click to expand...
Click to expand...
 
Hi George,

you can use either method

if you want to keep the crieria in the query, you can echo
that onto the report with a calculated control on the report

in the query
criteria -->
Between Date()-[Enter Number of Days] And Date()

textbox control on the report
ControlSource -->
=Reports!ReportName![Enter Number of Days]

where
ReportName is the name of the report
(that you have the calculated control on)

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



George said:
I haven't used a form to do a query before. I always used the query in design
view. None of the data that I have is manually entered. It comes from
outside/linked data bases. I simply query the data through access. Does this
affect your suggestion?
:

criteria -->
Between Date()-[Enter Number of Days:] And Date()

If you are planning to create a report, it would be best to
use a form to collect the information

create a blank form
Name it --> ReportMenu

create a textbox field with no Controlsource
Name--> NumDays

in your query
Between Date()-forms!ReportMenu!NumDays And Date()


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



George said:
query creteria:

Between Date()-"X" and Date()

I want to create a parameter query for the "-x" portion of these creteria.
But later on, in a report I, want to show the value of "X", example:

This report is valid for the last "X" days.
Click to expand...
Click to expand...
Click to expand...
 
Ok I'm making progress using a parameter query, but I have had to redefine my
creteria:

Area STATUSCHDT
STATUS
creteria [156]
"active" or "*pend*"
or [156] Date()-[Days Past] Date()
"expired" or canceled"

I have the parameter query down, but I can't get the calculated control,
Date()-[Days Past] Date(), to echo to my report.




strive4peace said:
Hi George,

you can use either method

if you want to keep the crieria in the query, you can echo
that onto the report with a calculated control on the report

in the query
criteria -->
Between Date()-[Enter Number of Days] And Date()

textbox control on the report
ControlSource -->
=Reports!ReportName![Enter Number of Days]

where
ReportName is the name of the report
(that you have the calculated control on)

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



George said:
I haven't used a form to do a query before. I always used the query in design
view. None of the data that I have is manually entered. It comes from
outside/linked data bases. I simply query the data through access. Does this
affect your suggestion?
:

criteria -->
Between Date()-[Enter Number of Days:] And Date()

If you are planning to create a report, it would be best to
use a form to collect the information

create a blank form
Name it --> ReportMenu

create a textbox field with no Controlsource
Name--> NumDays

in your query
Between Date()-forms!ReportMenu!NumDays And Date()


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



George Walters wrote:

query creteria:

Between Date()-"X" and Date()

I want to create a parameter query for the "-x" portion of these creteria.
But later on, in a report I, want to show the value of "X", example:

This report is valid for the last "X" days.
Click to expand...
Click to expand...
Click to expand...
 
Ok I'm making progress using a parameter query, but I have had to redefine my
creteria:

Area STATUSCHDT
STATUS
creteria [156]
"active" or "*pend*"
or [156] Date()-[Days Past] Date()
"expired" or canceled"

I have the parameter query down, but I can't get the calculated control,
Date()-[Days Past] Date(), to echo to my report.
Click to expand...

It is really much easier if you post the SQL of the query, or the
relevant part of the SQL (the WHERE clause in this case) rather than
trying to wrestle with word wrap.

Your criteria on Status appear to be wrong: to use wildcards such as *
you must use the LIKE operator, e.g.

LIKE "*active*" OR LIKE "*pend*"

And the syntax Date() - [Days Past] Date() appears to be simply
incorrect. Date() is a VBA function which returns today's date; a
human might interpret the phrase [Days Past] Date() as "the number of
days past today's date" but Access certaionly would not! What is the
intention of this expression? Might you instead want

DateDiff("d", [Days Past], Date())

or perhaps a DateAdd function?

John W. Vinson[MVP]
 
Let's start over on my parameter query creteria:`

My first row creteria is "active" for the field [STATUS] and let's say "127"
for [Area] field.
My second row creteria (or) is "expired for [STATUS] and "127" for [Area]
and Between Date()-[Past Days] and Date() for field [CHANGEDATE]

I have successuflly done the parameter this query.

The [Past Days] would be a number indicating somnething like 10 days ago.
It is the [Past Days] that I want to echo on my report and am having trouble
with
John Vinson said:
Ok I'm making progress using a parameter query, but I have had to redefine my
creteria:

Area STATUSCHDT
STATUS
creteria [156]
"active" or "*pend*"
or [156] Date()-[Days Past] Date()
"expired" or canceled"

I have the parameter query down, but I can't get the calculated control,
Date()-[Days Past] Date(), to echo to my report.
Click to expand...

It is really much easier if you post the SQL of the query, or the
relevant part of the SQL (the WHERE clause in this case) rather than
trying to wrestle with word wrap.

Your criteria on Status appear to be wrong: to use wildcards such as *
you must use the LIKE operator, e.g.

LIKE "*active*" OR LIKE "*pend*"

And the syntax Date() - [Days Past] Date() appears to be simply
incorrect. Date() is a VBA function which returns today's date; a
human might interpret the phrase [Days Past] Date() as "the number of
days past today's date" but Access certaionly would not! What is the
intention of this expression? Might you instead want

DateDiff("d", [Days Past], Date())

or perhaps a DateAdd function?

John W. Vinson[MVP]
Click to expand...
 
Let's start over on my parameter query creteria:`

My first row creteria is "active" for the field [STATUS] and let's say "127"
for [Area] field.
My second row creteria (or) is "expired for [STATUS] and "127" for [Area]
and Between Date()-[Past Days] and Date() for field [CHANGEDATE]

I have successuflly done the parameter this query.

The [Past Days] would be a number indicating somnething like 10 days ago.
It is the [Past Days] that I want to echo on my report and am having trouble
with
Click to expand...

I'm sorry, I don't understand what you're saying. Is [Past Days] a
date, ten days prior to today's date? Is it a number, the number of
days between some datefield in the table and today's date? Is it a
table field, or a parameter input by the user?

Again: as I requested... PLEASE POST THE SQL. We cannot see your
query, and attempting to explain the query grid layout is ambiguous
and unclear. The SQL may look like gibberish but it *IS* the query,
it's clear, and it's unambiguous (and it's worth learning to read!)

John W. Vinson[MVP]
 
Hi George,

this is your query criteria:
Between Date()-[Past Days] and Date()

you want to print a report, which I will refer to as
"ReportName" and you want the date range to be on your report

make a calculated control (textbox) on your report

= format(Date()-Reports!ReportName![Past Days], "m/d/yy") &
" to " & format(Date(), "m/d/yy")


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com



George said:
Let's start over on my parameter query creteria:`

My first row creteria is "active" for the field [STATUS] and let's say "127"
for [Area] field.
My second row creteria (or) is "expired for [STATUS] and "127" for [Area]
and Between Date()-[Past Days] and Date() for field [CHANGEDATE]

I have successuflly done the parameter this query.

The [Past Days] would be a number indicating somnething like 10 days ago.
It is the [Past Days] that I want to echo on my report and am having trouble
with
:

Ok I'm making progress using a parameter query, but I have had to redefine my
creteria:

Area STATUSCHDT
STATUS
creteria [156]
"active" or "*pend*"
or [156] Date()-[Days Past] Date()
"expired" or canceled"

I have the parameter query down, but I can't get the calculated control,
Date()-[Days Past] Date(), to echo to my report.
Click to expand...

It is really much easier if you post the SQL of the query, or the
relevant part of the SQL (the WHERE clause in this case) rather than
trying to wrestle with word wrap.

Your criteria on Status appear to be wrong: to use wildcards such as *
you must use the LIKE operator, e.g.

LIKE "*active*" OR LIKE "*pend*"

And the syntax Date() - [Days Past] Date() appears to be simply
incorrect. Date() is a VBA function which returns today's date; a
human might interpret the phrase [Days Past] Date() as "the number of
days past today's date" but Access certaionly would not! What is the
intention of this expression? Might you instead want

DateDiff("d", [Days Past], Date())

or perhaps a DateAdd function?

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Back
Top