Parameter Query

[Roger Bell]

I have 3 fields in a Data Base, Namely Day (1st, 2nd etc), Month, Year.

I have a query that requests the Day, then the Month and then the Year.
This means that the user needs to type the 3 entries to generate the required
certificate.
Is there any way the paremeters can be combined so that the user can just
type for example 2nd July 2008 to generate the certificate.

Thanks for any help

 

[Wayne-I-M]

Hi Roger

Access has the option to have a time/date field that you can format to
02/03/08 or 2 March 2008 etc

Is there a reason why you need 3 fields

If you use the date format you can always use a query to seperate the date
into
day month year

Oh and you should have a look at the list of restricted field names

If there "is" a valid reason why you need the 3 field post back with the
reason and you will get a more good answer

 

[Roger Bell]

Thanks for that Wayne

The certificate is preprinted and has preprinted areas for Day, Month, Year.
Consequently, the reason for seperate fields.

Any further help would be appreciated.

Many thanks
Roger

 

[Wayne-I-M]

Then what you need to do is to store the date and then use a function to
seperate the date in a query or in your report printing

Like this
txtMonth: Format([TableName]![date],"mmmm")
or
txtYear: DatePart("yyyy",[TableName]![date])
or
txtDay: DatePart("d",[TableName]![date])

Then - from your original question you would have in the criteria row of the
date column in your new query just put this
[Enter Date]

Hope this helps

 

[Roger Bell]

I am so sorry Wayne. As you can see, I really am a novice.

Are you saying that I should only have 1 field now in the Form for my date?

Thanks and sorry for being a nuisance

Then what you need to do is to store the date and then use a function to
seperate the date in a query or in your report printing

Like this
txtMonth: Format([TableName]![date],"mmmm")
or
txtYear: DatePart("yyyy",[TableName]![date])
or
txtDay: DatePart("d",[TableName]![date])

Then - from your original question you would have in the criteria row of the
date column in your new query just put this
[Enter Date]

Hope this helps
--
Wayne
Manchester, England.

Thanks for that Wayne

The certificate is preprinted and has preprinted areas for Day, Month, Year.
Consequently, the reason for seperate fields.

Any further help would be appreciated.

Many thanks
Roger

 

[Wayne-I-M]

If you already have lots of records it will be a little more complex (you can
use an update query to combine all the day month year fields into a date) but
if you only a few records you would be better just typeing in the dates.

Go to the table and create a new field called txtDate (don't just call it
date).
Add the dates into this new field

If you have not already done so - create a query based on the table

Create 3 callculated columns -
Right click in a blank build and select build
a box will popup
add this
txtDay: Format([TableName]![date],"dd")

then in a new column add this
txtMonth: Format([TableName]![date],"mmmm")

then in a new column add this
txtYear: Format([TableName]![date],"yyyy")

Note that I have not used DatePart as I asusme you only need the day month
year for a report which is not sorted by date (note that you will have 3
"text" columns now so they sort in a report in alphabetical order)

Also include the txtDate field in the query.

Open you form in design view and change the source of the form from the
table to the query. Add (drag from the view field list) the new txtdate
field and place it where you need it.

Use the 3 new txtday txtMonth and txtYear in your pre-printed reports

good luck and hope this helps you a little


--
Wayne
Manchester, England.

I am so sorry Wayne. As you can see, I really am a novice.

Are you saying that I should only have 1 field now in the Form for my date?

Thanks and sorry for being a nuisance

Then what you need to do is to store the date and then use a function to
seperate the date in a query or in your report printing

Like this
txtMonth: Format([TableName]![date],"mmmm")
or
txtYear: DatePart("yyyy",[TableName]![date])
or
txtDay: DatePart("d",[TableName]![date])

Then - from your original question you would have in the criteria row of the
date column in your new query just put this
[Enter Date]

Hope this helps
--
Wayne
Manchester, England.

Thanks for that Wayne

The certificate is preprinted and has preprinted areas for Day, Month, Year.
Consequently, the reason for seperate fields.

Any further help would be appreciated.

Many thanks
Roger

:

Hi Roger

Access has the option to have a time/date field that you can format to
02/03/08 or 2 March 2008 etc

Is there a reason why you need 3 fields

If you use the date format you can always use a query to seperate the date
into
day month year

Oh and you should have a look at the list of restricted field names

If there "is" a valid reason why you need the 3 field post back with the
reason and you will get a more good answer


--
Wayne
Manchester, England.



:

I have 3 fields in a Data Base, Namely Day (1st, 2nd etc), Month, Year.

I have a query that requests the Day, then the Month and then the Year.
This means that the user needs to type the 3 entries to generate the required
certificate.
Is there any way the paremeters can be combined so that the user can just
type for example 2nd July 2008 to generate the certificate.

Thanks for any help

 

[Wayne-I-M]

sorry mistake

in the query you need to change the name of the table to what it really is
like this


txtDay: Format([TableName]![txtdate],"dd")
txtMonth: Format([TableName]![txtdate],"mmmm")
txtYear: Format([TableName]![txtdate],"yyyy")

Change [TableName] to whatever you have used as the base table for the query

sorry

--
Wayne
Manchester, England.

If you already have lots of records it will be a little more complex (you can
use an update query to combine all the day month year fields into a date) but
if you only a few records you would be better just typeing in the dates.

Go to the table and create a new field called txtDate (don't just call it
date).
Add the dates into this new field

If you have not already done so - create a query based on the table

Create 3 callculated columns -
Right click in a blank build and select build
a box will popup
add this
txtDay: Format([TableName]![date],"dd")

then in a new column add this
txtMonth: Format([TableName]![date],"mmmm")

then in a new column add this
txtYear: Format([TableName]![date],"yyyy")

Note that I have not used DatePart as I asusme you only need the day month
year for a report which is not sorted by date (note that you will have 3
"text" columns now so they sort in a report in alphabetical order)

Also include the txtDate field in the query.

Open you form in design view and change the source of the form from the
table to the query. Add (drag from the view field list) the new txtdate
field and place it where you need it.

Use the 3 new txtday txtMonth and txtYear in your pre-printed reports

good luck and hope this helps you a little


--
Wayne
Manchester, England.

I am so sorry Wayne. As you can see, I really am a novice.

Are you saying that I should only have 1 field now in the Form for my date?

Thanks and sorry for being a nuisance

Then what you need to do is to store the date and then use a function to
seperate the date in a query or in your report printing

Like this
txtMonth: Format([TableName]![date],"mmmm")
or
txtYear: DatePart("yyyy",[TableName]![date])
or
txtDay: DatePart("d",[TableName]![date])

Then - from your original question you would have in the criteria row of the
date column in your new query just put this
[Enter Date]

Hope this helps
--
Wayne
Manchester, England.



:

Thanks for that Wayne

The certificate is preprinted and has preprinted areas for Day, Month, Year.
Consequently, the reason for seperate fields.

Any further help would be appreciated.

Many thanks
Roger

:

Hi Roger

Access has the option to have a time/date field that you can format to
02/03/08 or 2 March 2008 etc

Is there a reason why you need 3 fields

If you use the date format you can always use a query to seperate the date
into
day month year

Oh and you should have a look at the list of restricted field names

If there "is" a valid reason why you need the 3 field post back with the
reason and you will get a more good answer


--
Wayne
Manchester, England.



:

I have 3 fields in a Data Base, Namely Day (1st, 2nd etc), Month, Year.

I have a query that requests the Day, then the Month and then the Year.
This means that the user needs to type the 3 entries to generate the required
certificate.
Is there any way the paremeters can be combined so that the user can just
type for example 2nd July 2008 to generate the certificate.

Thanks for any help

 

[John Spencer]

And if you can't change the design of the table and the data, you can change
the query.

Assumption: the Year, Month, and Day fields are number fields.

Parameters [Enter the date] DateTime;
SELECT *
FROM Table
WHERE [YearField] = Year([Enter the date])
AND [MonthField] = Month([Enter the date])
AND [DayField] = Day([Enter the date])

In the query grid that would be something like:
Field: YearField
Criteria: Year([Enter the date])

Field: MonthField
Criteria: Month([Enter the date])

Field: DayField
Criteria: Day([Enter the date])

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County