Show day of week only

[Jill]

In my query, I need to show only the day of the week - example, Monday, April
6, 2099 would show only as 6 ... I have searched and searched and can't quite
figure it out.

 

[Dirk Goldgar]

In my query, I need to show only the day of the week - example, Monday,
April
6, 2099 would show only as 6 ... I have searched and searched and can't
quite
figure it out.

Um, it sounds like you're looking for the day of the month, not the day of
the week. For the day of the month, use the Day() function, as in:

SELECT Day([YourDateField]) FROM YourTable

If you really want the day of the week (a number from 1 to 7), use the
Weekday function, like this:

SELECT Weekday([YourDateField]) FROM YourTable

 

[Jill]

I am being incredibly slow regarding this. I am in the query. I have tried to
place this code both the format area of the property and in the criteria
area. It just is not working. This date field is a "Group By" ... is that
causing problems?

 

[Dirk Goldgar]

I am being incredibly slow regarding this. I am in the query. I have tried
to
place this code both the format area of the property and in the criteria
area. It just is not working. This date field is a "Group By" ... is that
causing problems?

My understanding is that you want to *show* the day number (whether of the
week or month) in the query results. Therefore, you need to put the
function expression in the Field line of the query design grid, thus
defining a calculated field. What you put there might look something like
this:

DayOfMonth: Day([YourDateField])

In the above, "DayOfMonth" is the name you assign to the calculated field,
and "YourDateField" should be changed to the name of the field that holds
the date.

 

[Jill]

Thanks Dirk.

You are right, I want the day of the month. I need to modify the date in the
query - I can't from the table because the date is used for other queries.

If I am in the query - where do I put that code? I have tried under
properties in the format field and under criteria. I am being a little slow
here.

In my query, I need to show only the day of the week - example, Monday,
April
6, 2099 would show only as 6 ... I have searched and searched and can't
quite
figure it out.

Um, it sounds like you're looking for the day of the month, not the day of
the week. For the day of the month, use the Day() function, as in:

SELECT Day([YourDateField]) FROM YourTable

If you really want the day of the week (a number from 1 to 7), use the
Weekday function, like this:

SELECT Weekday([YourDateField]) FROM YourTable

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Jill]

Dirk - thank you, thank you, thank you!!!

I am being incredibly slow regarding this. I am in the query. I have tried
to
place this code both the format area of the property and in the criteria
area. It just is not working. This date field is a "Group By" ... is that
causing problems?

My understanding is that you want to *show* the day number (whether of the
week or month) in the query results. Therefore, you need to put the
function expression in the Field line of the query design grid, thus
defining a calculated field. What you put there might look something like
this:

DayOfMonth: Day([YourDateField])

In the above, "DayOfMonth" is the name you assign to the calculated field,
and "YourDateField" should be changed to the name of the field that holds
the date.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Jill]

I have two separate queries - one for current year and one for prior year.
Each includes day of week and the invoice total for that day. I need to
create a report that shows for every day of the week the invoices generated
comparing current year to prior.

When I try to combine the queries - I can't because I have chosen record
fields that access cannot connect. Any suggestions?

Jill

Dirk - thank you, thank you, thank you!!!

I am being incredibly slow regarding this. I am in the query. I have tried
to
place this code both the format area of the property and in the criteria
area. It just is not working. This date field is a "Group By" ... is that
causing problems?

My understanding is that you want to *show* the day number (whether of the
week or month) in the query results. Therefore, you need to put the
function expression in the Field line of the query design grid, thus
defining a calculated field. What you put there might look something like
this:

DayOfMonth: Day([YourDateField])

In the above, "DayOfMonth" is the name you assign to the calculated field,
and "YourDateField" should be changed to the name of the field that holds
the date.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[KARL DEWEY]

Post the SQL of your queries. Open in design view, click on VIEW - SQL View,
highlight all, copy, and paste in a post.

I have two separate queries - one for current year and one for prior year.
Each includes day of week and the invoice total for that day. I need to
create a report that shows for every day of the week the invoices generated
comparing current year to prior.

When I try to combine the queries - I can't because I have chosen record
fields that access cannot connect. Any suggestions?

Jill

Dirk - thank you, thank you, thank you!!!

I am being incredibly slow regarding this. I am in the query. I have tried
to
place this code both the format area of the property and in the criteria
area. It just is not working. This date field is a "Group By" ... is that
causing problems?


My understanding is that you want to *show* the day number (whether of the
week or month) in the query results. Therefore, you need to put the
function expression in the Field line of the query design grid, thus
defining a calculated field. What you put there might look something like
this:

DayOfMonth: Day([YourDateField])

In the above, "DayOfMonth" is the name you assign to the calculated field,
and "YourDateField" should be changed to the name of the field that holds
the date.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I have two separate queries - one for current year and one for prior year.
Each includes day of week and the invoice total for that day. I need to
create a report that shows for every day of the week the invoices
generated
comparing current year to prior.

When I try to combine the queries - I can't because I have chosen record
fields that access cannot connect. Any suggestions?

We'd need to see the SQL of the two queries. Could you please open each
query in SQL View, and copy and paste the SQL of each query, along with the
name of the query, into a reply to this thread?

 

[Jill]

Thank you so much for your help.

Option 1: This is the SQL from the combined table
SELECT [Qry:_jbh_InvoiceTotalayCurrentYear].DayofMonthCurrent,
[Qry:_jbh_InvoiceTotalayPriorYear].DayofMonthPrior,
[Qry:_jbh_InvoiceTotalayCurrentYear].CurrentYrInvoiceAmount,
[Qry:_jbh_InvoiceTotalayPriorYear].PriorYrInvAmount,
IIf(IsNull([CurrentYrInvoiceAmount]),0,[CurrentYrInvoiceAmount])-IIf(IsNull([PriorYrInvAmount]),0,[PriorYrInvAmount]) AS Comparison
FROM (dbo_Clients LEFT JOIN [Qry:_jbh_InvoiceTotalayCurrentYear] ON
dbo_Clients.ID = [Qry:_jbh_InvoiceTotalayCurrentYear].ID) LEFT JOIN
[Qry:_jbh_InvoiceTotalayPriorYear] ON dbo_Clients.ID =
[Qry:_jbh_InvoiceTotalayPriorYear].ID
WHERE ((([Qry:_jbh_InvoiceTotalayCurrentYear].CurrentYrInvoiceAmount) Is
Not Null)) OR ((([Qry:_jbh_InvoiceTotalayPriorYear].PriorYrInvAmount) Is
Not Null))
ORDER BY [Qry:_jbh_InvoiceTotalayPriorYear].DayofMonthPrior;


Option 2: Here is the SQL from Current Year Summary followed by SQL from
Prior Year Summary

SELECT DISTINCTROW [Qry:_jbh_InvoiceTotalayCurrentYear].DayofMonthCurrent,
Sum([Qry:_jbh_InvoiceTotalayCurrentYear].CurrentYrInvoiceAmount) AS
SumOfCurrentYrInvoiceAmount
FROM [Qry:_jbh_InvoiceTotalayCurrentYear]
GROUP BY [Qry:_jbh_InvoiceTotalayCurrentYear].DayofMonthCurrent;


SELECT DISTINCTROW [Qry:_jbh_InvoiceTotalayPriorYear].DayofMonthPrior,
Sum([Qry:_jbh_InvoiceTotalayPriorYear].PriorYrInvAmount) AS
SumOfPriorYrInvAmount
FROM [Qry:_jbh_InvoiceTotalayPriorYear]
GROUP BY [Qry:_jbh_InvoiceTotalayPriorYear].DayofMonthPrior;

 

[Dirk Goldgar]

Thank you so much for your help.

Option 1: This is the SQL from the combined table
SELECT [Qry:_jbh_InvoiceTotalayCurrentYear].DayofMonthCurrent,
[Qry:_jbh_InvoiceTotalayPriorYear].DayofMonthPrior,
[Qry:_jbh_InvoiceTotalayCurrentYear].CurrentYrInvoiceAmount,
[Qry:_jbh_InvoiceTotalayPriorYear].PriorYrInvAmount,
IIf(IsNull([CurrentYrInvoiceAmount]),0,[CurrentYrInvoiceAmount])-IIf(IsNull([PriorYrInvAmount]),0,[PriorYrInvAmount])
AS Comparison
FROM (dbo_Clients LEFT JOIN [Qry:_jbh_InvoiceTotalayCurrentYear] ON
dbo_Clients.ID = [Qry:_jbh_InvoiceTotalayCurrentYear].ID) LEFT JOIN
[Qry:_jbh_InvoiceTotalayPriorYear] ON dbo_Clients.ID =
[Qry:_jbh_InvoiceTotalayPriorYear].ID
WHERE ((([Qry:_jbh_InvoiceTotalayCurrentYear].CurrentYrInvoiceAmount) Is
Not Null)) OR ((([Qry:_jbh_InvoiceTotalayPriorYear].PriorYrInvAmount) Is
Not Null))
ORDER BY [Qry:_jbh_InvoiceTotalayPriorYear].DayofMonthPrior;


Option 2: Here is the SQL from Current Year Summary followed by SQL from
Prior Year Summary

SELECT DISTINCTROW
[Qry:_jbh_InvoiceTotalayCurrentYear].DayofMonthCurrent,
Sum([Qry:_jbh_InvoiceTotalayCurrentYear].CurrentYrInvoiceAmount) AS
SumOfCurrentYrInvoiceAmount
FROM [Qry:_jbh_InvoiceTotalayCurrentYear]
GROUP BY [Qry:_jbh_InvoiceTotalayCurrentYear].DayofMonthCurrent;


SELECT DISTINCTROW [Qry:_jbh_InvoiceTotalayPriorYear].DayofMonthPrior,
Sum([Qry:_jbh_InvoiceTotalayPriorYear].PriorYrInvAmount) AS
SumOfPriorYrInvAmount
FROM [Qry:_jbh_InvoiceTotalayPriorYear]
GROUP BY [Qry:_jbh_InvoiceTotalayPriorYear].DayofMonthPrior;

I don't know where your "Option 1" and "Option 2" come from; "Option 2"
looks like the only thing we've discussed so far. If you have the two
queries you identified as "Current Year Summary" and "Prior Year Summary",
then to get a full comparison you're going to need a union of two sets:
first, all the records from [Current Year Summary] (whether they have
matches in [Prior Year Summary] or not), and second, any records from [Prior
Year Summary] that don't have matches in [Current Year Summary]. The SQL
for the first set would be like this:

SELECT
DayOfMonthCurrent As DayOfMonth,
SumOfCurrentYrInvoiceAmt As CurrentYrAmt,
Nz(SumOfPriorYrsInvoiceAmt, 0) As PriorYrAmt,
SumOfCurrentYrInvoiceAmt - Nz(SumOfPriorYrsInvoiceAmt, 0)
AS Comparison
FROM
[Current Year Summary]
LEFT JOIN
[Prior Year Summary]
ON DayOfMonthCurrent = DayOfMonthPrior;

The SQL for the second set would be like this:

SELECT
DayOfMonthPrior As DayOfMonth,
0 As CurrentYrAmt,
SumOfPriorYrsInvoiceAmt As PriorYrAmt,
-SumOfPriorYrsInvoiceAmt AS Comparison
FROM
[Prior Year Summary]
LEFT JOIN
[Current Year Summary]
ON DayOfMonthPrior = DayOfMonthCurrent
WHERE
DayOfMonthCurrent Is Null;

So the union of these sets will be given by this SQL:

SELECT
DayOfMonthCurrent As DayOfMonth,
SumOfCurrentYrInvoiceAmt As CurrentYrAmt,
Nz(SumOfPriorYrsInvoiceAmt, 0) As PriorYrAmt,
SumOfCurrentYrInvoiceAmt - Nz(SumOfPriorYrsInvoiceAmt, 0)
AS Comparison
FROM
[Current Year Summary]
LEFT JOIN
[Prior Year Summary]
ON DayOfMonthCurrent = DayOfMonthPrior
UNION ALL
SELECT
DayOfMonthPrior As DayOfMonth,
0 As CurrentYrAmt,
SumOfPriorYrsInvoiceAmt As PriorYrAmt,
-SumOfPriorYrsInvoiceAmt AS Comparison
FROM
[Prior Year Summary]
LEFT JOIN
[Current Year Summary]
ON DayOfMonthPrior = DayOfMonthCurrent
WHERE
DayOfMonthCurrent Is Null
ORDER BY DayOfMonth;

All of that is "air SQL", and completely untested, but something like that
ought to do the trick.

I see in your "Option 1" that you are introducing the idea of grouping by
Client. You didn't mention that before, and it would make things a bit more
complicated than the simple case above. Because I don't know what it is you
really want to do, I'm not going to get into that now.

By the way, the names you've given to your queries, incorporating as they do
nonstandard characters such as ":" and " ", make the SQL much more
cumbersome to work with. If you have any control of the matter, I strongly
recommend you revise these names.