Create an input box for update queries macro

[jesZee]

Hi,

I have five update queries that I have to change the date daily. So, to
reduce my work (opening and closing) the queries, I have put them into
a macro and update the date by having the criteria as "DATE()-1".

However, I have a little problem because I have to make sure I run this
macro daily and after the weekend, I have to change the date manually
in order to run the macro accurately.

Can anyone give me some idea of how to either:

1. have a pop-up box that ask for the date input and then update my
queries accordingly
2. some other method to do this more efficiently

Btw, I am not at all good in VB or scripting so maybe need a LOT of
guidance if I have to do it that way.

 

[Steve Schapel]

JesZee,

Put an unbound textbox on a form which will be open at the time you run
the macro. In that textbox, you can enter the required date criteria.
If you like you can set its Default Value property to Date()-1 so you
only need to change it sometimes. Then, in the Criteria of the query,
in the place of your existing date criteria, refer to the textbox on the
form, using syntax such as...
[Forms]![NameOfForm]![NameOfTextbox]

 

[Jeff L]

Create a module an put this in it:

Public Function GetDate() As Date
If Weekday(Date) = vbMonday Then
GetDate = Date - 3
Else
GetDate = Date - 1
End If
End Function

Then in the criteria of your date fields in your queries, put
GetDate().

Hope that helps!

 

[jesZee]

Hey Jeff & Steve,

Thanks so much for the guidance.

Both of them work - I chose Jeff cos that way i don't need to change
any of the dates even if I am running Monday report for Sat & Sun.

I learned two new ways!! So AWESOME...thank you guys.

J.

 

[Steve Schapel]

JesZee,

Yes, Jeff's idea is an excellent way to go.

If you like, you could combine the best of both ideas. In other words,
do Jeff's function, but also still use the unbound textbox on the form
approach that I suggested. Set the Default Value of the textbox to
GetDate(), so in most cases you won't have to do anything as regards the
criteria, but at the same time leaves the option open to manually change
the criteria date in unusual circumstances such as public holiday, or
you're running late and miss a day, or whatever.

 

[jesZee]

Steve,

You predicted my obstacle because this is what happened after the long
labor weekend.

Thanks a LOT~!!!!!

JesZ

 

[jesZee]

Hey Steve,

Tried out the combined solution today and it don't work. I have to key
in the date into the pop-up form otherwise it will not give me any
values.

HELP!!!

Thanks.

J

 

[Steve Schapel]

JesZee

Did you follow Jeff's method to create a function in a standard module,
using code like this?...

Public Function GetDate() As Date
If Weekday(Date) = vbMonday Then
GetDate = Date - 3
Else
GetDate = Date - 1
End If
End Function

And then, in design view of your form, with the textbox where the date
criteria is being referenced, did you set its Format property to a date
format, and set its Default Value property to...
GetDate()
And we assume this textbox is unbound, right? I.e. it's Control Source
property is blank?

And so, when the form first opens, what is displayed in the textbox?
Nothing?

 

[jesZee]

Steve,

The form opens up just fine with the correct date reflected in it. The
problem is when I insert that form in my update query, a pop-up box
called "Enter Parameter Value" and I have to manually enter the date
otherwise no result will appear.

Btw, can I have a date range instead of just one date?

I know I am giving you more work but I just want to save an extra step
while you reply to me.

THANKS A ZILLION, STEVE!

J

 

[Steve Schapel]

JesZee,

Ok, thanks for the further explanation. What is the Criteria you have
put in the update query? And what precisely is the Parameter that is
being asked for in the 'Enter Parameter Value' dialog? Maybe it would
be a good idea to post back with the SQL view of the update query.

Can you give me a more detailed example of what you mean with the date
range?

 

[Steve Schapel]

JesZee,

Ok, thanks for the further explanation. What is the Criteria you have
put in the update query? And what precisely is the Parameter that is
being asked for in the 'Enter Parameter Value' dialog? Maybe it would
be a good idea to post back with the SQL view of the update query.

Can you give me a more detailed example of what you mean with the date
range?

 

[jesZee]

Steve,

This is the SQL:

SELECT [RD Non Fill Table to Analyze].Mfgpno, IIf([Quality 1 or 2] Is
Not Null,"QC",IIf([QM11] Is Not Null,"Snag",IIf([Kits] Is Not
Null,"Kits",IIf([Supercession] Is Not Null,"Supercession",IIf([ZMNM] Is
Not Null,"New",IIf([S or NS] Is Null,"Non-Stock",IIf([S or NS] Is Not
Null,"Stock","XXXXX"))))))) AS Category
FROM [RD Non Fill Table to Analyze]
WHERE ((([RD Non Fill Table to Analyze].[Run Date])=[Forms]![Insert
Date form]![Insert Date]));


In my criteria,I have put in [Forms]![Insert Date form]![Insert Date]
as you suggested and the Parameter Value pop-up box was just blank so I
have to insert a date into it for the query to run properly.

As for the date range - I was looking for having something like this
"Between #2006/09/01# and #2006/09/15#" without having to change it
everytime I run that particular query.

Hope this clear up the confusion.

THANKS SO MUCH for your help STEVE!

J.

 

[Steve Schapel]

JesZee,

Well, it sort of clears up the confusion. This is not the SQL of an
Update Query, which is what I was expecting. And a 'Enter Parameter
Value' dialog will always be asking you for a particular parameter,
which is what I was hoping you could tell me. It is written just above
the textbox where you enter the value. If it is actually asking for the
paramteter:
[Forms]![Insert Date form]![Insert Date]
.... then the most usual cause for this would be a spelling error. Can
you check that the Name of the form is precisely:
Insert Date form
.... and the Name of the tetxbox (where you have the GetDate() function)
is precisely:
Insert Date
Also, check the properties of the Insert Date textbox, and set the
Format property to a valid date format, if it isn't already.

With the date range idea, do you mean you want two textboxes on the
form, with a date entered in each, and to be able to refer to them both,
for the date range, in the query criteria? If so, the answer is yes,
this is no problem. Your query criteria in the date field will be
something like this...
Between [Forms]![Insert Date form]![DateFrom] And [Forms]![Insert Date
form]![DateTo]
(where DateFrom and DateTo are the names of the textboxes).

 

[jesZee]

Steve,

As I have mentioned in the beginning, I am an amatuer when it comes to
coding so I just got the SQL from the drop list. I made this update
query the dummy way i.e. pulling down the field I want and select
"Update Query".

I have double checked the name of my form and the name of the textbox
and they are the same as what I have given you. As for the Format
property, I have set it to "General Date".

What more can I do, Steve?

THanks.

J.

JesZee,

Well, it sort of clears up the confusion. This is not the SQL of an
Update Query, which is what I was expecting. And a 'Enter Parameter
Value' dialog will always be asking you for a particular parameter,
which is what I was hoping you could tell me. It is written just above
the textbox where you enter the value. If it is actually asking for the
paramteter:
[Forms]![Insert Date form]![Insert Date]
... then the most usual cause for this would be a spelling error. Can
you check that the Name of the form is precisely:
Insert Date form
... and the Name of the tetxbox (where you have the GetDate() function)
is precisely:
Insert Date
Also, check the properties of the Insert Date textbox, and set the
Format property to a valid date format, if it isn't already.

With the date range idea, do you mean you want two textboxes on the
form, with a date entered in each, and to be able to refer to them both,
for the date range, in the query criteria? If so, the answer is yes,
this is no problem. Your query criteria in the date field will be
something like this...
Between [Forms]![Insert Date form]![DateFrom] And [Forms]![Insert Date
form]![DateTo]
(where DateFrom and DateTo are the names of the textboxes).

--
Steve Schapel, Microsoft Access MVP

Steve,

This is the SQL:

SELECT [RD Non Fill Table to Analyze].Mfgpno, IIf([Quality 1 or 2] Is
Not Null,"QC",IIf([QM11] Is Not Null,"Snag",IIf([Kits] Is Not
Null,"Kits",IIf([Supercession] Is Not Null,"Supercession",IIf([ZMNM] Is
Not Null,"New",IIf([S or NS] Is Null,"Non-Stock",IIf([S or NS] Is Not
Null,"Stock","XXXXX"))))))) AS Category
FROM [RD Non Fill Table to Analyze]
WHERE ((([RD Non Fill Table to Analyze].[Run Date])=[Forms]![Insert
Date form]![Insert Date]));


In my criteria,I have put in [Forms]![Insert Date form]![Insert Date]
as you suggested and the Parameter Value pop-up box was just blank so I
have to insert a date into it for the query to run properly.

As for the date range - I was looking for having something like this
"Between #2006/09/01# and #2006/09/15#" without having to change it
everytime I run that particular query.

Hope this clear up the confusion.

THANKS SO MUCH for your help STEVE!

 

[Steve Schapel]

JesZee,

Hey, that's exactly the same way that I make an Update Query! I
don't think it's a dummy way.

So, are you confirming that the 'Enter Parameter Value' dialog is asking
for "[Forms]![Insert Date form]![Insert Date]"?

Er, the [Insert Date form] is open at the time that the query is being
run, right?

Is it possible to email the mdb file to me, so I can look at it? If so,
send to steves at mvps dot org along with the steps you take to get the
problem.

 

[jesZee]

Steve,

One of your sentence makes my day!!!

I did not know I need to open the form in order for the query to work
properly. So now, its finally working and I am glad to hear a MVP like
you still uses the same way I am creating my update query!!

THANKS A GADZILLION TIMES!

Now, as for the date range - all I have to do is create two unbound
text boxes in the form but what do I put in the other new text box for
the "Default Value"? What do I need to change in my GetDate module???

Thanks in advance.

J.
p/s: do you still want me to e-mail you the mdb?

JesZee,

Hey, that's exactly the same way that I make an Update Query! I
don't think it's a dummy way.

So, are you confirming that the 'Enter Parameter Value' dialog is asking
for "[Forms]![Insert Date form]![Insert Date]"?

Er, the [Insert Date form] is open at the time that the query is being
run, right?

Is it possible to email the mdb file to me, so I can look at it? If so,
send to steves at mvps dot org along with the steps you take to get the
problem.

--
Steve Schapel, Microsoft Access MVP

Steve,

As I have mentioned in the beginning, I am an amatuer when it comes to
coding so I just got the SQL from the drop list. I made this update
query the dummy way i.e. pulling down the field I want and select
"Update Query".

I have double checked the name of my form and the name of the textbox
and they are the same as what I have given you. As for the Format
property, I have set it to "General Date".

What more can I do, Steve?

 

[Steve Schapel]

JesZee

One of your sentence makes my day!!!


I did not know I need to open the form in order for the query to work
properly.

Well, it's like this... Via [Forms]![Insert Date form]![Insert Date],
the query is trying to use the value of the [Insert Date] control on the
[Insert Date form] form, and such value does not exist if the form is
not open.

THANKS A GADZILLION TIMES!

You're most welcome.

Now, as for the date range - all I have to do is create two unbound
text boxes in the form but what do I put in the other new text box for
the "Default Value"? What do I need to change in my GetDate module???

Well, that depends on what you want the date range to be! Can you give
an example?

p/s: do you still want me to e-mail you the mdb?

No, not necessary now!

 

[jesZee]

I have two different date ranges:

1. Last week range as of on Monday i.e. if today is Monday (Sept 25),
the date range = between Sept 18 to Sept 24.

2. Past 8 weeks range i.e. if today is Monday (Sept 25), the date range
= between Sept 24 to Sept 17.

I still need that GetDate () function though so how can I incorporate
these two ranges.

THANKS STEVE!

J.

JesZee

One of your sentence makes my day!!!


I did not know I need to open the form in order for the query to work
properly.

Well, it's like this... Via [Forms]![Insert Date form]![Insert Date],
the query is trying to use the value of the [Insert Date] control on the
[Insert Date form] form, and such value does not exist if the form is
not open.

THANKS A GADZILLION TIMES!

You're most welcome.

Now, as for the date range - all I have to do is create two unbound
text boxes in the form but what do I put in the other new text box for
the "Default Value"? What do I need to change in my GetDate module???

Well, that depends on what you want the date range to be! Can you give
an example?

p/s: do you still want me to e-mail you the mdb?

No, not necessary now!

 

[Steve Schapel]

JesZee,

Well, we'll get there I'm sure. But for now, you've lost me I'm afraid.
The GetDate() function was supposed to return the previous working
day. So if today is Monday 25th, GetDate() would give you Friday 22nd.
But in your examples you are still using 24th, i.e. a Sunday? And in
your second example, did you mean 8 days rather than 8 weeks? And if
so, what are the 2 optional date ranges all about? One of the previous
7 days, and one for the previous 8 days? Thanks for any light you can
shed

 

[jesZee]

Steve,

1. Last week range as of on Monday i.e. if today is Monday (Sept 25),
Ok, this is an example. yes, the GetDate() function has the code
written that if today is Monday, then Date -3 because I need to run
Friday's report and then when I run Sat & Sun report on Monday, I will
change the Date to Date-2 and Date-1. For the rest of the week,
starting from Tues, I don't have to change anything.
That's why I want to get a pop-up box for me to insert the date when it
is Monday and I want to run Sat & Sun report or when it was a long
weekend, like you suggested earlier.

2. Past 8 weeks range i.e. if today is Monday (Sept 25), the date
rangeTotally my BAD! So sorry....I meant to say that the date range is
between July 24 to Sept 17 (again another example).

Apologize for replying to you so late since you were so prompt in your
reply. I was swamped with getting my queries right so I can get out the
reports. U have any advice or books to recommend for me to polish up my
MS Access skills.

THANKS A GADZILLION, STEVE!!!

J