Display Saturday and Sunday from Calendar

[Guest]

This is the current SQL query I have. It only displays Saturday and not
Sunday. I need it to display both (SATURDAY and SUNDAY).

SELECT format(date_1, 'dddd') AS days_of_week,
format(date_1, 'mmmm dd, yyyy') AS calendar_date
FROM sec1011_calendar_1
WHERE format(date_1, 'dddd') = 'saturday' and 'sunday'
ORDER BY n;

My output is: Saturday February 26, 2015
Saturday March 5, 2015
etc...

 

[Guest]

hi,

Change your WHERE clause to read WHERE format(date_1, 'dddd') = 'saturday'
OR 'sunday' instead of

WHERE format(date_1, 'dddd') = 'saturday' and 'sunday'

I am thinking that there will not be records in which there is a value of
Saturday AND Sunday, but rather there will be records with Saturday OR Sunday.

geebee

 

[Guest]

I made the change from AND to OR in the WHERE clause, but now it gives me all
the days of the week and date.

I want it to give me only yhe SATURDAY and SUNDAYS to include the date.

 

[Guest]

I also need to include a BLANK LINE between the WEEKS. If you can help with
that as well, I will greatly appreciate it.

 

[Guest]

hi,

This will work better...

SELECT date_1,
format(date_1, 'mmmm dd, yyyy') AS calendar_date
FROM sec1011_calendar_1
WHERE (((Weekday([date_1])) Not In ('1','7')))
ORDER BY n;

Not sure what you mean by putting space between the weeks. You post deals
with the days, and it looks like your output does to. If you would like for
a blank row to be between each record in a report, simply put a box or
something right underneath the field.

Hope this helps,
geebee

 

[Guest]

With the line you posted, I made the changes to read like this:

SELECT format(date_1, 'dddd') AS days_of_week,
format(date_1, 'mmmm dd, yyyy') AS calendar_date
FROM sec1011_calendar_1
WHERE (((weekday([date_1])) IN ('1','7')))
ORDER BY n;

It now gives me the correct data. (SATURADAY and SUNDAY)

***I still need to come up with how to put a blank line between weeks using
an SQL statement or within the same statement above.***

hi,

This will work better...

SELECT date_1,
format(date_1, 'mmmm dd, yyyy') AS calendar_date
FROM sec1011_calendar_1
WHERE (((Weekday([date_1])) Not In ('1','7')))
ORDER BY n;

Not sure what you mean by putting space between the weeks. You post deals
with the days, and it looks like your output does to. If you would like for
a blank row to be between each record in a report, simply put a box or
something right underneath the field.

Hope this helps,
geebee

I also need to include a BLANK LINE between the WEEKS. If you can help with
that as well, I will greatly appreciate it.

 

[Guest]

With the line you posted, I made the changes to read like this:

SELECT format(date_1, 'dddd') AS days_of_week,
format(date_1, 'mmmm dd, yyyy') AS calendar_date
FROM sec1011_calendar_1
WHERE (((weekday([date_1])) IN ('1','7')))
ORDER BY n;

It now gives me the correct data. (SATURADAY and SUNDAY)

***I still need to come up with how to put a blank line between weeks using
an SQL statement or within the same statement above.***

hi,

This will work better...

SELECT date_1,
format(date_1, 'mmmm dd, yyyy') AS calendar_date
FROM sec1011_calendar_1
WHERE (((Weekday([date_1])) Not In ('1','7')))
ORDER BY n;

Not sure what you mean by putting space between the weeks. You post deals
with the days, and it looks like your output does to. If you would like for
a blank row to be between each record in a report, simply put a box or
something right underneath the field.

Hope this helps,
geebee

I also need to include a BLANK LINE between the WEEKS. If you can help with
that as well, I will greatly appreciate it.

 

[Guest]

With the line you posted, I made the changes to read like this:

SELECT format(date_1, 'dddd') AS days_of_week,
format(date_1, 'mmmm dd, yyyy') AS calendar_date
FROM sec1011_calendar_1
WHERE (((weekday([date_1])) IN ('1','7')))
ORDER BY n;

It now gives me the correct data. (SATURADAY and SUNDAY)

***I still need to come up with how to put a blank line between weeks using
an SQL statement or within the same statement above.***

hi,

This will work better...

SELECT date_1,
format(date_1, 'mmmm dd, yyyy') AS calendar_date
FROM sec1011_calendar_1
WHERE (((Weekday([date_1])) Not In ('1','7')))
ORDER BY n;

Not sure what you mean by putting space between the weeks. You post deals
with the days, and it looks like your output does to. If you would like for
a blank row to be between each record in a report, simply put a box or
something right underneath the field.

Hope this helps,
geebee

I also need to include a BLANK LINE between the WEEKS. If you can help with
that as well, I will greatly appreciate it.

 

[Neil Sunderland]

***I still need to come up with how to put a blank line between weeks using
an SQL statement or within the same statement above.***

Why do you want to do that?

It sound like the sort of thing you would use a Report for, not a
Query. SQL is for pulling data from your database, not formatting it.

 

[Guest]

The requirement I have is to craete the sql statement that lists all the data
in the correct format. That is done. It also staes to place a blank line
between the weeks giving them seperation.

I cannot find anything in the book showing how to perform that. I am
assuming it needs to be placed some where within this statement giving it a
seperation between weeks:

SELECT format(date_1, 'mmmm dd, yyyy') AS calendar_date,
format(date_1, 'ddd') AS abrev_day
FROM sec1011_calendar_1
WHERE (((weekday([date_1])) IN ('1','7')))
ORDER BY n;

**I do not see a Reply to line, only a By: line. Are you wanting me to
email you to your address?

 

[Neil Sunderland]

The requirement I have is to craete the sql statement that lists all the data
in the correct format. That is done. It also staes to place a blank line
between the weeks giving them seperation.

I cannot find anything in the book showing how to perform that. I am
assuming it needs to be placed some where within this statement giving it a
seperation between weeks:

You won't be able to do that within your SQL; even if you did manage
to create some blank rows (it is possible, but it's fiddly...), they
wont appear in the right places as the ORDER BY clause will force all
the 'blank lines' to sort to the top.

As I said, formatting is not something you should try to do within a
query, but it is something that Reports are designed for.

Try changing your query to this:

SELECT DateDiff("ww",'2000-01-01',date_1) AS week_nbr,
format(date_1, 'dddd') AS days_of_week,
format(date_1, 'mmmm dd, yyyy') AS calendar_date
FROM sec1011_calendar_1
WHERE (((weekday([date_1])) IN ('1','7')))
ORDER BY n;

This will add another field in your query showing how many weeks there
are between 1st Jan 2000 and the date in each row.

Now click on the 'Reports', and click 'Create report by using Wizard'.
Use the 'Tables/Queries' list to select your query, then click the
[>>] button to put all the fields on your report. Click 'Next'.

On the next screen, highlight the 'week_nbr' field, and click the [>]
button. This indicates that you want to group your report by the week
number. Click 'Next'.

The next screen allows you pick the sort order. The 'calendar_date'
field will do fine. Click 'Next'.

The next two screens allow you to pick a layout and style for your
report. You can choose, this time! Once you're past them, you can
enter a name for your report, and click 'Finish' to see how it looks.

It's probably a bit fussier than you want: the quickest way to get
that is to design the form - click 'View' then 'Design View' on the
toolbar, then select everything in the 'week_nbr Header' section and
press the delete key. Now click 'View' and 'Layout Preview' and it
should be close to what you originally wanted.

You'll probably want to tidy up the Page Header and Page Footer
sections as well, but I'll leave you to figure that out yourself
(nothing personal: it's just that Ravens v Broncos is about to start!)

I do not see a Reply to line, only a By: line. Are you wanting me to
email you to your address?

No, that's just part of my signature for people who want (or need) to
send me an email. It just indicates that you need to use the address
in the 'Reply-to' header of my post and not the one in the 'From'
header. I wouldn't worry about it

 

[John Vinson]

The requirement I have is to craete the sql statement that lists all the data
in the correct format. That is done. It also staes to place a blank line
between the weeks giving them seperation.

That is an absurd requirement. No reasonable developer would use a
*QUERY* to do this; it's the wrong tool.

This sounds like whoever prepared the course materials is like a
carpenter teaching an apprentice how to drive nails with a wrench.


John W. Vinson[MVP]

 

[Guest]

It is probably an error within the book. I have found a few already as it is.

Thanks for all your help (to all of you).