By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,933 Members | 1,243 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,933 IT Pros & Developers. It's quick & easy.

sql for an A97 query that'll return 6 future consecutive dates 20-25 days from now

P: n/a
MLH
Anybody's solution would be appreciated. Pls,
do not pause to write anything for this. I'm not
looking for that kind of a handout. I have an idea
about how to do it, but I wanted to see if anyone
has already done it and is proud of their achieve-
ment and willing to share it.

The courts order public sales of property in judgments
in my county. They require the auctioneers to schedule
future dates and advertise them (Sundays not allowed).
So, I want a combo-box to list 5 or 6 choices of dates
20-25 days from now (5 if any one of the days is a Sunday).

Am seeking the SQL to feed the combo-box.

Many thx.
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
MLH
This isn't quite it, but its getting there. All I
need to do now is figure out how to get them
in a column (instead of a row) and omit any
Sunday.

SELECT TOP 1 DateAdd("d",20,Int(Now())) AS 20DaysOut,
DateAdd("d",21,Int(Now())) AS 21DaysOut, DateAdd("d",22,Int(Now())) AS
22DaysOut, DateAdd("d",23,Int(Now())) AS 23DaysOut,
DateAdd("d",24,Int(Now())) AS 24DaysOut, DateAdd("d",25,Int(Now())) AS
25DaysOut
FROM tblVehicleJobs;

By the way, FROM tblVehicleJobs can be FROM AnyExistingTable. The
QBE grid requires I put in a table before it'll output a dynaset.
Nov 13 '05 #2

P: n/a
jv
How about populating the combo box using code. First change the
RowSource Type property of the combo box to Value List, then place the
following code in the Form's OnLoad Event.

dim i as integer
for i=20 to 25
if weekday(DateAdd("d",i,Date() ))<>vbSunday then
cboControlName.rowsource=cboControlName.rowsource &
DateAdd("d",i,Date() & ";"
end if
next i

Nov 13 '05 #3

P: n/a
jv
You could also do a queriey, such as:
SELECT TOP 1 DateAdd("d",20,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",20,Date())))>1))
UNION SELECT TOP 1 DateAdd("d",21,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",21,Date())))>1))
UNION SELECT TOP 1 DateAdd("d",22,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",22,Date())))>1))
UNION SELECT TOP 1 DateAdd("d",23,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",23,Date())))>1))
UNION SELECT TOP 1 DateAdd("d",24,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",24,Date())))>1))
UNION SELECT TOP 1 DateAdd("d",25,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",25,Date())))>1))

Nov 13 '05 #4

P: n/a

Personally, I'd use a either a dynamic ValueList, rather then a
dropdown, assuming you _have_ to have a dropdown.

If not, pop over to Stephen Lebans site <http://www.lebans.com/> and
pick up his Calendar form <http://www.lebans.com/monthcalendar.htm>.
There's an Access 97 version.

Users usually can pick non-Sundays when they can "see" a calendar.
You'll want to put in code to make sure however. Users occasionally
don't watch what they're doing!
On Tue, 05 Jul 2005 13:28:20 -0400, MLH <CR**@NorthState.net> wrote:
This isn't quite it, but its getting there. All I
need to do now is figure out how to get them
in a column (instead of a row) and omit any
Sunday.
SELECT TOP 1 DateAdd("d",20,Int(Now())) AS 20DaysOut,
DateAdd("d",21,Int(Now())) AS 21DaysOut, DateAdd("d",22,Int(Now())) AS
22DaysOut, DateAdd("d",23,Int(Now())) AS 23DaysOut,
DateAdd("d",24,Int(Now())) AS 24DaysOut, DateAdd("d",25,Int(Now())) AS
25DaysOut
FROM tblVehicleJobs;
By the way, FROM tblVehicleJobs can be FROM AnyExistingTable. The
QBE grid requires I put in a table before it'll output a dynaset.


Nov 13 '05 #5

P: n/a
jv wrote:
You could also do a queriey, such as:
SELECT TOP 1 DateAdd("d",20,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",20,Date())))>1))
UNION SELECT TOP 1 DateAdd("d",21,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",21,Date())))>1))
UNION SELECT TOP 1 DateAdd("d",22,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",22,Date())))>1))
UNION SELECT TOP 1 DateAdd("d",23,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",23,Date())))>1))
UNION SELECT TOP 1 DateAdd("d",24,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",24,Date())))>1))
UNION SELECT TOP 1 DateAdd("d",25,Date()) FROM tblCodes WHERE
(((Weekday(DateAdd("d",25,Date())))>1))


I really liked jv's solutions. She seems to think a lot like I do. So
I decided to try to extend the train of thought she started, by using a
subquery.

Without the Sunday condition, assuming tblX has at least six records
and some kind of ID field, you could use:

SELECT DateAdd("d", (SELECT Count(A.ID) + 20 FROM tblX AS A WHERE A.ID
< tblX.ID), Date()) As ComboDate FROM tblX WHERE tblX.ID <= 6;

to get 20 through 25 days added to the current date labeled as
ComboDate.

I thought it was interesting that a subquery could be used as an
argument to a function, but it makes sense. Perhaps this can be
exploited further.

I tried putting WeekDay around the entire subquery to try to get the
Sunday condition factored in (I tried square brackets around ComboDate
first and nixed the idea of using two queries):

SELECT DateAdd("d", (SELECT Count(A.ID) + 20 FROM tblX AS A WHERE A.ID
< tblX.ID), Date()) AS ComboDate FROM tblX WHERE tblX.ID <= 6 AND
WeekDay(DateAdd("d", (SELECT Count(A.ID) + 20 FROM tblX AS A WHERE A.ID
< tblX.ID), Date())) <> 1;

To my surprise that seemed to work without getting an error message.
Still a little dubious, I tried changing the <> 1 at the end to = 5.
That returned just the Thursday date. I also tried using later dates
as input. It seemed to exclude the Sundays properly. So the SQL seems
to be doing the right thing and could be used to populate a combobox.
This SQL solution becomes more attractive if the range of dates is
larger causing a Union Query to become long.

James A. Fortune

Nov 13 '05 #6

P: n/a
MLH
<snip>
All these were excellent suggestions, but I gotta hand it to ya,
James, this one is clever & sweet.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxx

SELECT DateAdd("d", (SELECT Count(A.ID) + 20 FROM tblX AS A WHERE A.ID
< tblX.ID), Date()) AS ComboDate FROM tblX WHERE tblX.ID <= 6 AND
WeekDay(DateAdd("d", (SELECT Count(A.ID) + 20 FROM tblX AS A WHERE A.ID
< tblX.ID), Date())) <> 1;

To my surprise that seemed to work without getting an error message.
Still a little dubious, I tried changing the <> 1 at the end to = 5.
That returned just the Thursday date. I also tried using later dates
as input. It seemed to exclude the Sundays properly. So the SQL seems
to be doing the right thing and could be used to populate a combobox.
This SQL solution becomes more attractive if the range of dates is
larger causing a Union Query to become long.

James A. Fortune


Nov 13 '05 #7

P: n/a
MLH
Jim, I love the SQL. However, I'm having a problem after making
a selection in a combo-box whose record source is based on the
query output. The error I see after making a selection is ...

The value you entered isn't valid for this field. For example, you
may have entered text in a numeric field or a number that is larger
than the FieldSize setting permits.

How can I shake this error?

SELECT DateAdd("d",(SELECT Count(A.ID) + 20 FROM tblX AS A WHERE A.ID
< tblX.ID),Date()) AS ComboDate
FROM tblX
WHERE (((tblX.ID)<=6) AND ((Weekday(DateAdd("d",(SELECT Count(A.ID) +
20 FROM tblX AS A WHERE A.ID < tblX.ID),Date())))<>1));

Nov 13 '05 #8

P: n/a
MLH wrote:
Jim, I love the SQL. However, I'm having a problem after making
a selection in a combo-box whose record source is based on the
query output. The error I see after making a selection is ...

The value you entered isn't valid for this field. For example, you
may have entered text in a numeric field or a number that is larger
than the FieldSize setting permits.

How can I shake this error?

SELECT DateAdd("d",(SELECT Count(A.ID) + 20 FROM tblX AS A WHERE A.ID
< tblX.ID),Date()) AS ComboDate
FROM tblX
WHERE (((tblX.ID)<=6) AND ((Weekday(DateAdd("d",(SELECT Count(A.ID) +
20 FROM tblX AS A WHERE A.ID < tblX.ID),Date())))<>1));


If the combobox is bound to a date field, try using CDate around the
DateAdd part. Access is supposed to know that DateAdd returns a date
but CDate usually coerces SQL into knowing that the field is a date
field. Another idea is to set the field type of ComboDate before the
SELECT statement in SQL.

James A. Fortune

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.