UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Help With Immediate If In Query - Segregate Saturdays, Access 2013    
 
   
bakersburg9
post Nov 28 2017, 05:03 PM
Post#1



Posts: 4,956
Joined: 2-November 04
From: Downey, CA


My company doesn't work Sundays, and has a skeleton crew on Saturdays - I have a report in Access that shows #jobs by date - on Saturdays, the # is far less, but the viewer of the report/query doesn't know that - it just looks like low activity on that date - I tried building it into my query, but can't get the syntax right, and I couldn't find anything on the 'net

This doesn't work:
CODE
Notes: IIF(Weekday(Date())="6",[JobsWorked]![Sched Dte],”Saturday”," ")


the date field name is "Sched Dte" from the JobsWorked table
Go to the top of the page
 
doctor9
post Nov 28 2017, 05:09 PM
Post#2


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


bakersburg9,

Try removing the quotation marks that around the 6. The Weekday() function returns an integer, not a string.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
theDBguy
post Nov 28 2017, 05:10 PM
Post#3


Access Wiki and Forums Moderator
Posts: 71,236
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Date() refers to the current date. I think you meant to use the name of the field, which is [Sched Dte], in your expression instead. For example:

Notes: IIf(Weekday([Sched Dte])=6, "Saturday", [Sched Dte])

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
BruceM
post Nov 29 2017, 07:44 AM
Post#4


UtterAccess VIP
Posts: 7,022
Joined: 24-May 10
From: Downeast Maine


Perhaps the Format function can be of help. I don't know exactly what you want to see for the other days of the week, but Format([SomeDateField],"dddd") will give you the full day of the week. Conditional formatting can be used to distinguish the Saturday dates.
Go to the top of the page
 
bakersburg9
post Nov 29 2017, 03:59 PM
Post#5



Posts: 4,956
Joined: 2-November 04
From: Downey, CA


Both suggestions just returned the date , i.e. 11/29/2017
Go to the top of the page
 
MadPiet
post Nov 29 2017, 04:17 PM
Post#6



Posts: 2,264
Joined: 27-February 09



I'm still not clear on what you want to see if a count or whatever happens for a Saturday. Do you want to hide those? Include the day of the week on the X-axis? The other odd thing is that in one case of your IIF() you're returning a string, and in the other, a number. Why is that?
Go to the top of the page
 
doctor9
post Nov 29 2017, 04:41 PM
Post#7


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


bakersburg9,

> Both suggestions just returned the date , i.e. 11/29/2017

Just to be clear, you're saying that this expression:

CODE
Notes: IIF(Weekday(Date())=6,[JobsWorked]![Sched Dte],”Saturday”," ")


...Is returning a date value?

If not, please post your SQL.

Hope this helps,

Dennis

EDIT: Oh wait, now I see. You have an IF test, followed by THREE choices; the date field, the word "Saturday", and a single space. You need to clearly explain what you want this expression to show, I think. An IF test should only have TWO choices, one if the IF test is TRUE and one if the IF test is FALSE.

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
projecttoday
post Nov 29 2017, 04:46 PM
Post#8


UtterAccess VIP
Posts: 8,689
Joined: 10-February 04
From: South Charleston, WV


In the U. S., Saturday is the 7th day of the week.

CODE
SELECT Table1.date1 & " " & Iif (Weekday(date1)=7,"Saturday","")
FROM Table1;


replacing Table1 with the name of your table and date1 with the name of the date field in the table.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
bakersburg9
post Nov 30 2017, 11:41 AM
Post#9



Posts: 4,956
Joined: 2-November 04
From: Downey, CA


Robert – that just returned a date

Dennis,
There is just two choices in the way I had it – Saturday, and blank spaces…. “Saturday”,”<space> “

Here’s my SQL:
CODE
SELECT JobCallUpdates.[Sched Dte], Count(JobCallUpdates.[Sched Dte]) AS [Calls By Date]
FROM JobCallUpdates
GROUP BY JobCallUpdates.[Sched Dte];

I’m showing what it looks like when ran – I want an additional column that shows if Saturday – actually, it would be nice if it would return a 0 for Sunday
Really appreciate all the help! cool.gif

I will attach a screen shot if I can edit this post - my pc is acting funky... :-(
Attached File(s)
Attached File  Saturday_SundayGIF.gif ( 27.94K )Number of downloads: 1
 
Go to the top of the page
 
projecttoday
post Nov 30 2017, 11:59 AM
Post#10


UtterAccess VIP
Posts: 8,689
Joined: 10-February 04
From: South Charleston, WV


It returns a date or a date with "Saturday" to the right of it if the date is Saturday.

CODE
SELECT Table1.date1, IIf(Weekday(date1)=7,"Saturday",Iif(Weekday(date1)=1,"0","")) AS DayOfWeek
FROM Table1;


Will do "Saturday" in a separate column. Or "0" if it's Sunday.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
doctor9
post Nov 30 2017, 01:20 PM
Post#11


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


bakersburg,

> There is just two choices in the way I had it – Saturday, and blank spaces…. “Saturday”,”<space> “
> Notes: IIF(Weekday(Date())="6",[JobsWorked]![Sched Dte],”Saturday”," ")

Note that you had THREE choices. The first one is [JobsWorked]![Sched Dte]. The second is "Saturday". The third is the single space.

The SQL you posted doesn't include this expression anywhere. Try adding the expression without the quotes around the 6 and without the [JobsWorked]![Sched Dte] bit.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bakersburg9
post Nov 30 2017, 02:23 PM
Post#12



Posts: 4,956
Joined: 2-November 04
From: Downey, CA


Dennis,
The SQL I posted is the actual code that WORKS - the top half of my screen scrape shows what it returns now - the bottom half is what I want it to return when I add the code I can't figure out the syntax for :-(

I tried the other suggestion, but maybe it was operator error - I copied my db, and renamed the table "Table1," and renamed "Sched Dte" "Date1" - maybe I didn't understand how to tweek my actual field names to match his code exactly - it ran, but just returned dates
Go to the top of the page
 
projecttoday
post Nov 30 2017, 02:46 PM
Post#13


UtterAccess VIP
Posts: 8,689
Joined: 10-February 04
From: South Charleston, WV


You didn't see the word "Saturday"? Double check and make sure at least one of the dates is for a Saturday.

Give me a minute and I'll post my mock-up.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
projecttoday
post Nov 30 2017, 02:52 PM
Post#14


UtterAccess VIP
Posts: 8,689
Joined: 10-February 04
From: South Charleston, WV


Attached.
Attached File(s)
Attached File  DateNamedb.zip ( 21.97K )Number of downloads: 2
 

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
doctor9
post Nov 30 2017, 02:55 PM
Post#15


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


bakersburg9,

> maybe I didn't understand how to tweek my actual field names to match his code exactly - it ran, but just returned dates

This is why I asked that you post the SQL, so we can see why it's returning a date. Posting the SQL that doesn't include the problem doesn't help us find the problem. If you removed the [JobsWorked]![Sched Dte], portion of the expression, it should not return a date at all.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bakersburg9
post Nov 30 2017, 05:22 PM
Post#16



Posts: 4,956
Joined: 2-November 04
From: Downey, CA


Thanks, Robert! I think I'm close - I've done a really bad job explaining this - I had a query that rolled up the totals by date - the reason I wanted to add the note for "Saturday" is there are far less calls - I wanted the reader to understand the reason for the low volume is because of the skeleton crew on Saturdays - that's it - my thinking was all crazy when I was talking about Sundays and Holidays - that made no sense - don't know what I was thinking.

So I attached a sample db - and it has my "old" query that works, but doesn't have that additional field with the note: "Saturday" if the date is, in fact, a Saturday. I've tried to essentially "combine" the two, but I can't get the syntax right confused.gif
Attached File(s)
Attached File  SaturdayChallenge.zip ( 284.14K )Number of downloads: 4
 
Go to the top of the page
 
projecttoday
post Nov 30 2017, 06:05 PM
Post#17


UtterAccess VIP
Posts: 8,689
Joined: 10-February 04
From: South Charleston, WV


Oh, you are doing an aggregate (COUNT) query. (How many posts have there been barking up the Iif syntax tree?) Aggregate queries require a GROUP BY clause:

CODE
SELECT JobCallUpdates.[Sched Dte], IIf(Weekday([Sched Dte])=7,"Saturday",IIf(Weekday([Sched Dte])=1,"0","")) AS Notes, Count(JobCallUpdates.[Sched Dte]) AS [Calls By Date]
FROM JobCallUpdates
GROUP BY
JobCallUpdates.[Sched Dte],
IIf(Weekday([Sched Dte])=7,"Saturday",IIf(Weekday([Sched Dte])=1,"0",""))

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
bakersburg9
post Nov 30 2017, 06:59 PM
Post#18



Posts: 4,956
Joined: 2-November 04
From: Downey, CA


John, That’s it !! Thank you !!!

I tweeked it a bit…
CODE
SELECT JobCallUpdates.[Sched Dte] AS Scheduled, Count(JobCallUpdates.[Sched Dte]) AS [# Calls Made], IIf(Weekday([Sched Dte])=7,"Saturday",IIf(Weekday([Sched Dte])=1,"0","")) AS Notes
FROM JobCallUpdates
GROUP BY JobCallUpdates.[Sched Dte], IIf(Weekday([Sched Dte])=7,"Saturday",IIf(Weekday([Sched Dte])=1,"0",""))
ORDER BY JobCallUpdates.[Sched Dte] DESC;

That dog’ll hunt !

thanks.gif

Steve
Attached File(s)
Attached File  JobCallsByDateJpeg.jpg ( 33.98K )Number of downloads: 0
 
Go to the top of the page
 
projecttoday
post Nov 30 2017, 07:32 PM
Post#19


UtterAccess VIP
Posts: 8,689
Joined: 10-February 04
From: South Charleston, WV


John?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
bakersburg9
post Dec 9 2017, 11:36 PM
Post#20



Posts: 4,956
Joined: 2-November 04
From: Downey, CA


Sorry - ROBERT LOL - Senior moment (I'm 59)
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 10:24 PM