query to pull up all jobs due on WEd. for example

[Guest]

I have a list of jobs in a jobs table and it list which days the jobs need to
be done. For example some are just M, some M, W, F , some just W etc.

I am trying to grab the current date on the Form ( an unbound field where I
use datepart to grab the current DAY

I have created a query for the jobs list and would like for the criteria to
pull up all jobs say due on Wednesday. I can put the criteria in as Like
"*w*" and get what I want. However I would like for it to know that it is
wednesday(or whatever the CURRENT DAY is) and grab it from the form(or just
from the query-whatever would work) and use this field in the Query Criteria.
What am I missing?? How do I write the criteria in the query.

Thanks,
Barb

 

[Guest]

Create a text box and put data sorce as =Format(Now(),"ddd")

or put it as the criteria in the query (Remember to put the field in the
same format)

This will give you the current day of the week in 3 digit format "Mon" etc

Hope this helps

Trev

 

[Guest]

Trevor,

Thanks for your help. I am still not clear on what to do here. If I have
the text box on the form with datasource as set as below. I ALSO need the
query behind the scenes to use what is in that textbox as the criteria and
want to find say Thu- if it is listed in the Frequecny of the job by ITSELF
or for example if it is listed say as T, TH.

Do I use this both in the query and on the form and for the query critera
would the below info find both Th on its own and in a list say M, T, Th.

Thanks so much again for your help,
BArb

 

[Guest]

Hi Babs,

One problem you have is that you do not use the standard naming convention
that the system uses ie Day of the week. The system uses 3 standards as
follows:-

1) Mon Format([a date],"ddd")

2) Monday Format([a date],"dddd")

3) 2 (Sun =1, Mon = 2 etc) Format([a date],"w")


Since you use non standard you will have to write code in a module or code
to check the standard against your non standard day of the week. May I
suggest you use standard to make life easier. Ask for help on format
function (User Defined) on dates for more information. (If you still want to
use non standard reply and will have to write a module procudure for this.)

After you have changed to standard just put this in the criteria of the
query and it will pick up just the current day of the week.

Format(Now(),"ddd")

Hope this helps.

 

[Guest]

sorry just got back to working on this. I put your inital recommended code
in the citeria. I changed the days to the standard convention of Tue for
Tuesday instead of T- no problem with that. However it pulls up all of the
records with frequency Tue ONLY if Tue is part of more freq. for example Tue,
Thu those records are not coming up. how can I modify the criteria to grab
these records???

Thanks,
Barb

Hi Babs,

One problem you have is that you do not use the standard naming convention
that the system uses ie Day of the week. The system uses 3 standards as
follows:-

1) Mon Format([a date],"ddd")

2) Monday Format([a date],"dddd")

3) 2 (Sun =1, Mon = 2 etc) Format([a date],"w")


Since you use non standard you will have to write code in a module or code
to check the standard against your non standard day of the week. May I
suggest you use standard to make life easier. Ask for help on format
function (User Defined) on dates for more information. (If you still want to
use non standard reply and will have to write a module procudure for this.)

After you have changed to standard just put this in the criteria of the
query and it will pick up just the current day of the week.

Format(Now(),"ddd")

Hope this helps.

Trevor,

Thanks for your help. I am still not clear on what to do here. If I have
the text box on the form with datasource as set as below. I ALSO need the
query behind the scenes to use what is in that textbox as the criteria and
want to find say Thu- if it is listed in the Frequecny of the job by ITSELF
or for example if it is listed say as T, TH.

Do I use this both in the query and on the form and for the query critera
would the below info find both Th on its own and in a list say M, T, Th.

Thanks so much again for your help,
BArb

 

[Guest]

Hi babs,

Not sure where you are up to so lets try this:-

Create a Query with the fields you want that then add a date to the Field
section like:

ddd_Date:Format([Date to Check against],"ddd")

[Date to Check against] is a date field you want to use


Then in Criteria you can put:

In("Tue") for Tue

or

In("Wed") for Wed

or

In("Tue","Thu","Sat") for Tue, Wed & Sat

or

Not In("Tue","Thu","Sat") for Sun, Mon, Wed, Fri

Hope this helps

Trev

sorry just got back to working on this. I put your inital recommended code
in the citeria. I changed the days to the standard convention of Tue for
Tuesday instead of T- no problem with that. However it pulls up all of the
records with frequency Tue ONLY if Tue is part of more freq. for example Tue,
Thu those records are not coming up. how can I modify the criteria to grab
these records???

Thanks,
Barb

Hi Babs,

One problem you have is that you do not use the standard naming convention
that the system uses ie Day of the week. The system uses 3 standards as
follows:-

1) Mon Format([a date],"ddd")

2) Monday Format([a date],"dddd")

3) 2 (Sun =1, Mon = 2 etc) Format([a date],"w")


Since you use non standard you will have to write code in a module or code
to check the standard against your non standard day of the week. May I
suggest you use standard to make life easier. Ask for help on format
function (User Defined) on dates for more information. (If you still want to
use non standard reply and will have to write a module procudure for this.)

After you have changed to standard just put this in the criteria of the
query and it will pick up just the current day of the week.

Format(Now(),"ddd")

Hope this helps.

Trevor,

Thanks for your help. I am still not clear on what to do here. If I have
the text box on the form with datasource as set as below. I ALSO need the
query behind the scenes to use what is in that textbox as the criteria and
want to find say Thu- if it is listed in the Frequecny of the job by ITSELF
or for example if it is listed say as T, TH.

Do I use this both in the query and on the form and for the query critera
would the below info find both Th on its own and in a list say M, T, Th.

Thanks so much again for your help,
BArb

:

Create a text box and put data sorce as =Format(Now(),"ddd")

or put it as the criteria in the query (Remember to put the field in the
same format)

This will give you the current day of the week in 3 digit format "Mon" etc

Hope this helps

Trev

:

I have a list of jobs in a jobs table and it list which days the jobs need to
be done. For example some are just M, some M, W, F , some just W etc.

I am trying to grab the current date on the Form ( an unbound field where I
use datepart to grab the current DAY

I have created a query for the jobs list and would like for the criteria to
pull up all jobs say due on Wednesday. I can put the criteria in as Like
"*w*" and get what I want. However I would like for it to know that it is
wednesday(or whatever the CURRENT DAY is) and grab it from the form(or just
from the query-whatever would work) and use this field in the Query Criteria.
What am I missing?? How do I write the criteria in the query.

Thanks,
Barb

 

[Guest]

Trever,

Thanks for your help I am still not seeming to explain what I need clearly

Right now I want to grab Today's DAY and pull up all records in the
query(criteria) with frequecy of doing all the jobs due today say- Wed .

I have in the criteria for the Frequency field =Format(Now(),"ddd"). It
does pull up ALL Wed ONLY frequency but want it to pull up if it says Mon,
Wed, Fri or Mon, Wed or any Combination.


Can I somehow use the In with what is already in the criteria row for the
frequecy field?
Doesn't seem to be working.

In("=Format(Now(),"ddd")" )

any ideas?
Thanks,
Barb

Hi babs,

Not sure where you are up to so lets try this:-

Create a Query with the fields you want that then add a date to the Field
section like:

ddd_Date:Format([Date to Check against],"ddd")

[Date to Check against] is a date field you want to use


Then in Criteria you can put:

In("Tue") for Tue

or

In("Wed") for Wed

or

In("Tue","Thu","Sat") for Tue, Wed & Sat

or

Not In("Tue","Thu","Sat") for Sun, Mon, Wed, Fri

Hope this helps

Trev

sorry just got back to working on this. I put your inital recommended code
in the citeria. I changed the days to the standard convention of Tue for
Tuesday instead of T- no problem with that. However it pulls up all of the
records with frequency Tue ONLY if Tue is part of more freq. for example Tue,
Thu those records are not coming up. how can I modify the criteria to grab
these records???

Thanks,
Barb

Hi Babs,

One problem you have is that you do not use the standard naming convention
that the system uses ie Day of the week. The system uses 3 standards as
follows:-

1) Mon Format([a date],"ddd")

2) Monday Format([a date],"dddd")

3) 2 (Sun =1, Mon = 2 etc) Format([a date],"w")


Since you use non standard you will have to write code in a module or code
to check the standard against your non standard day of the week. May I
suggest you use standard to make life easier. Ask for help on format
function (User Defined) on dates for more information. (If you still want to
use non standard reply and will have to write a module procudure for this.)

After you have changed to standard just put this in the criteria of the
query and it will pick up just the current day of the week.

Format(Now(),"ddd")

Hope this helps.


:

Trevor,

Thanks for your help. I am still not clear on what to do here. If I have
the text box on the form with datasource as set as below. I ALSO need the
query behind the scenes to use what is in that textbox as the criteria and
want to find say Thu- if it is listed in the Frequecny of the job by ITSELF
or for example if it is listed say as T, TH.

Do I use this both in the query and on the form and for the query critera
would the below info find both Th on its own and in a list say M, T, Th.

Thanks so much again for your help,
BArb

:

Create a text box and put data sorce as =Format(Now(),"ddd")

or put it as the criteria in the query (Remember to put the field in the
same format)

This will give you the current day of the week in 3 digit format "Mon" etc

Hope this helps

Trev

:

I have a list of jobs in a jobs table and it list which days the jobs need to
be done. For example some are just M, some M, W, F , some just W etc.

I am trying to grab the current date on the Form ( an unbound field where I
use datepart to grab the current DAY

I have created a query for the jobs list and would like for the criteria to
pull up all jobs say due on Wednesday. I can put the criteria in as Like
"*w*" and get what I want. However I would like for it to know that it is
wednesday(or whatever the CURRENT DAY is) and grab it from the form(or just
from the query-whatever would work) and use this field in the Query Criteria.
What am I missing?? How do I write the criteria in the query.

Thanks,
Barb

 

[Guest]

Hi Babs,

Now we are changing things, You want to select the dates and then run a
query on the selection made. The best way to do this would be using a
selection criteria from a combo box on a form.

Step 1. Create a combo box on a form that selects the Day you want. (I
Have
used form1

Step 2 . Amend Query to include this creteria to (No = required)
Eval("([Forms]![Form1]![TestDate])")

The combo box(TestDate) now contains the day of the week

If you want to include multi creteria use another combo box for step one and
use next line in criteria for step 2 and so on.

Hope this helps

Trev

Trever,

Thanks for your help I am still not seeming to explain what I need clearly

Right now I want to grab Today's DAY and pull up all records in the
query(criteria) with frequecy of doing all the jobs due today say- Wed .

I have in the criteria for the Frequency field =Format(Now(),"ddd"). It
does pull up ALL Wed ONLY frequency but want it to pull up if it says Mon,
Wed, Fri or Mon, Wed or any Combination.


Can I somehow use the In with what is already in the criteria row for the
frequecy field?
Doesn't seem to be working.

In("=Format(Now(),"ddd")" )

any ideas?
Thanks,
Barb

Hi babs,

Not sure where you are up to so lets try this:-

Create a Query with the fields you want that then add a date to the Field
section like:

ddd_Date:Format([Date to Check against],"ddd")

[Date to Check against] is a date field you want to use


Then in Criteria you can put:

In("Tue") for Tue

or

In("Wed") for Wed

or

In("Tue","Thu","Sat") for Tue, Wed & Sat

or

Not In("Tue","Thu","Sat") for Sun, Mon, Wed, Fri

Hope this helps

Trev

sorry just got back to working on this. I put your inital recommended code
in the citeria. I changed the days to the standard convention of Tue for
Tuesday instead of T- no problem with that. However it pulls up all of the
records with frequency Tue ONLY if Tue is part of more freq. for example Tue,
Thu those records are not coming up. how can I modify the criteria to grab
these records???

Thanks,
Barb

:

Hi Babs,

One problem you have is that you do not use the standard naming convention
that the system uses ie Day of the week. The system uses 3 standards as
follows:-

1) Mon Format([a date],"ddd")

2) Monday Format([a date],"dddd")

3) 2 (Sun =1, Mon = 2 etc) Format([a date],"w")


Since you use non standard you will have to write code in a module or code
to check the standard against your non standard day of the week. May I
suggest you use standard to make life easier. Ask for help on format
function (User Defined) on dates for more information. (If you still want to
use non standard reply and will have to write a module procudure for this.)

After you have changed to standard just put this in the criteria of the
query and it will pick up just the current day of the week.

Format(Now(),"ddd")

Hope this helps.


:

Trevor,

Thanks for your help. I am still not clear on what to do here. If I have
the text box on the form with datasource as set as below. I ALSO need the
query behind the scenes to use what is in that textbox as the criteria and
want to find say Thu- if it is listed in the Frequecny of the job by ITSELF
or for example if it is listed say as T, TH.

Do I use this both in the query and on the form and for the query critera
would the below info find both Th on its own and in a list say M, T, Th.

Thanks so much again for your help,
BArb

:

Create a text box and put data sorce as =Format(Now(),"ddd")

or put it as the criteria in the query (Remember to put the field in the
same format)

This will give you the current day of the week in 3 digit format "Mon" etc

Hope this helps

Trev

:

I have a list of jobs in a jobs table and it list which days the jobs need to
be done. For example some are just M, some M, W, F , some just W etc.

I am trying to grab the current date on the Form ( an unbound field where I
use datepart to grab the current DAY

I have created a query for the jobs list and would like for the criteria to
pull up all jobs say due on Wednesday. I can put the criteria in as Like
"*w*" and get what I want. However I would like for it to know that it is
wednesday(or whatever the CURRENT DAY is) and grab it from the form(or just
from the query-whatever would work) and use this field in the Query Criteria.
What am I missing?? How do I write the criteria in the query.

Thanks,
Barb

 

[Michael White]

It seems to me you should be using a 3 letter identification for day of the
week to distinguish between Tuesday, Thursday, Saturday and Sunday.

Here is my solution:
Three lettered day:
Criteria [field] = CStr(Format(Date(),"ddd"))
1 Lettered day
Left(CStr(Format(Date(),"ddd")),1)

Either one will work for criteria on an Access Database, if you are using
SQL server, then you need to do something completely different.