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

Having trouble with query...

P: n/a
MLH
I have a query with a single table. One of the table fields appearing
in the QBE grid is [NPPubDay] containing values from 1 to 127.

Then I have the following calculated field named [Published]:
Published: IIf((1 And [NPPubDay])=1,"Sunday ","") & IIf((2 And
[NPPubDay])=2,"Monday ","") & IIf((4 And [NPPubDay])=4,"Tuesday ","")
& IIf((8 And [NPPubDay])=8,"Wednesday ","") & IIf((16 And
[NPPubDay])=16,"Thursday ","") & IIf((32 And [NPPubDay])=32,"Friday
","") & IIf((64 And [NPPubDay])=64,"Saturday","")

When the query runs - I'm getting nothing in the [Published] field.
What am I doing wrong?

I was expecting stuff like...
Monday Wednesday Friday
Sunday Wednesday
Sunday Monday Tuesday Wednesday Thursday Friday Saturday
Tuesday Thursday
Monday Tuesday Wednesday Thursday Friday

But, as I said, I'm getting nothing.
Feb 19 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
MLH
Thanks for taking a stab at it. But that's not gonna do.
If the value of [NPPubDay] field is 127, I must have the
following 56-character string appear in the [Published]
field:
"Sunday Monday Tuesday Wednesday Thursday Friday Saturday"
That IS one value in one field.

If the value of [NPPubDay] field is 7, I must have the
following 21-character string appear in the [Published]
field:
"Sunday Monday Tuesday"
That is ALSO one value in one field.
Feb 20 '08 #2

P: n/a
MLH
So, can you see now, that in my query, I'm attempting to take a value
like 10 that might appear in the [NPPubDay] field and represent that
value of 10 in the [Published] field with "Bob Jack" (which is an
8-character string).
Feb 20 '08 #3

P: n/a
MLH <CR**@NorthState.netwrote in
news:ac********************************@4ax.com:
I have a query with a single table. One of the table fields appearing
in the QBE grid is [NPPubDay] containing values from 1 to 127.

Then I have the following calculated field named [Published]:
Published: IIf((1 And [NPPubDay])=1,"Sunday ","") & IIf((2 And
[NPPubDay])=2,"Monday ","") & IIf((4 And [NPPubDay])=4,"Tuesday ","")
& IIf((8 And [NPPubDay])=8,"Wednesday ","") & IIf((16 And
[NPPubDay])=16,"Thursday ","") & IIf((32 And [NPPubDay])=32,"Friday
","") & IIf((64 And [NPPubDay])=64,"Saturday","")

When the query runs - I'm getting nothing in the [Published] field.
What am I doing wrong?

I was expecting stuff like...
Monday Wednesday Friday
Sunday Wednesday
Sunday Monday Tuesday Wednesday Thursday Friday Saturday
Tuesday Thursday
Monday Tuesday Wednesday Thursday Friday

But, as I said, I'm getting nothing.
Perhaps a public function which is more easily tested would be more
convenient?

Public Function WeekdayNames(ByVal NPPubDay&)
Dim z&
On Error Resume Next
For z = 0 To 6
If (NPPubDay And 2 ^ z) Then _
WeekdayNames = WeekdayNames & " " & WeekdayName(z + 1)
Next z
WeekdayNames = Trim(WeekdayNames)
End Function

0
1 Sunday
2 Monday
3 Sunday Monday
4 Tuesday
5 Sunday Tuesday
6 Monday Tuesday
7 Sunday Monday Tuesday
8 Wednesday
9 Sunday Wednesday
10 Monday Wednesday
11 Sunday Monday Wednesday
12 Tuesday Wednesday
13 Sunday Tuesday Wednesday
14 Monday Tuesday Wednesday
15 Sunday Monday Tuesday Wednesday
16 Thursday
17 Sunday Thursday
18 Monday Thursday
19 Sunday Monday Thursday
20 Tuesday Thursday
21 Sunday Tuesday Thursday
22 Monday Tuesday Thursday
23 Sunday Monday Tuesday Thursday
24 Wednesday Thursday
25 Sunday Wednesday Thursday
26 Monday Wednesday Thursday
27 Sunday Monday Wednesday Thursday
28 Tuesday Wednesday Thursday
29 Sunday Tuesday Wednesday Thursday
30 Monday Tuesday Wednesday Thursday
31 Sunday Monday Tuesday Wednesday Thursday
32 Friday
33 Sunday Friday
34 Monday Friday
35 Sunday Monday Friday
36 Tuesday Friday
37 Sunday Tuesday Friday
38 Monday Tuesday Friday
39 Sunday Monday Tuesday Friday
40 Wednesday Friday
41 Sunday Wednesday Friday
42 Monday Wednesday Friday
43 Sunday Monday Wednesday Friday
44 Tuesday Wednesday Friday
45 Sunday Tuesday Wednesday Friday
46 Monday Tuesday Wednesday Friday
47 Sunday Monday Tuesday Wednesday Friday
48 Thursday Friday
49 Sunday Thursday Friday
50 Monday Thursday Friday
51 Sunday Monday Thursday Friday
52 Tuesday Thursday Friday
53 Sunday Tuesday Thursday Friday
54 Monday Tuesday Thursday Friday
55 Sunday Monday Tuesday Thursday Friday
56 Wednesday Thursday Friday
57 Sunday Wednesday Thursday Friday
58 Monday Wednesday Thursday Friday
59 Sunday Monday Wednesday Thursday Friday
60 Tuesday Wednesday Thursday Friday
61 Sunday Tuesday Wednesday Thursday Friday
62 Monday Tuesday Wednesday Thursday Friday
63 Sunday Monday Tuesday Wednesday Thursday Friday
64 Saturday
65 Sunday Saturday
66 Monday Saturday
67 Sunday Monday Saturday
68 Tuesday Saturday
69 Sunday Tuesday Saturday
70 Monday Tuesday Saturday
71 Sunday Monday Tuesday Saturday
72 Wednesday Saturday
73 Sunday Wednesday Saturday
74 Monday Wednesday Saturday
75 Sunday Monday Wednesday Saturday
76 Tuesday Wednesday Saturday
77 Sunday Tuesday Wednesday Saturday
78 Monday Tuesday Wednesday Saturday
79 Sunday Monday Tuesday Wednesday Saturday
80 Thursday Saturday
81 Sunday Thursday Saturday
82 Monday Thursday Saturday
83 Sunday Monday Thursday Saturday
84 Tuesday Thursday Saturday
85 Sunday Tuesday Thursday Saturday
86 Monday Tuesday Thursday Saturday
87 Sunday Monday Tuesday Thursday Saturday
88 Wednesday Thursday Saturday
89 Sunday Wednesday Thursday Saturday
90 Monday Wednesday Thursday Saturday
91 Sunday Monday Wednesday Thursday Saturday
92 Tuesday Wednesday Thursday Saturday
93 Sunday Tuesday Wednesday Thursday Saturday
94 Monday Tuesday Wednesday Thursday Saturday
95 Sunday Monday Tuesday Wednesday Thursday Saturday
96 Friday Saturday
97 Sunday Friday Saturday
98 Monday Friday Saturday
99 Sunday Monday Friday Saturday
100 Tuesday Friday Saturday
101 Sunday Tuesday Friday Saturday
102 Monday Tuesday Friday Saturday
103 Sunday Monday Tuesday Friday Saturday
104 Wednesday Friday Saturday
105 Sunday Wednesday Friday Saturday
106 Monday Wednesday Friday Saturday
107 Sunday Monday Wednesday Friday Saturday
108 Tuesday Wednesday Friday Saturday
109 Sunday Tuesday Wednesday Friday Saturday
110 Monday Tuesday Wednesday Friday Saturday
111 Sunday Monday Tuesday Wednesday Friday Saturday
112 Thursday Friday Saturday
113 Sunday Thursday Friday Saturday
114 Monday Thursday Friday Saturday
115 Sunday Monday Thursday Friday Saturday
116 Tuesday Thursday Friday Saturday
117 Sunday Tuesday Thursday Friday Saturday
118 Monday Tuesday Thursday Friday Saturday
119 Sunday Monday Tuesday Thursday Friday Saturday
120 Wednesday Thursday Friday Saturday
121 Sunday Wednesday Thursday Friday Saturday
122 Monday Wednesday Thursday Friday Saturday
123 Sunday Monday Wednesday Thursday Friday Saturday
124 Tuesday Wednesday Thursday Friday Saturday
125 Sunday Tuesday Wednesday Thursday Friday Saturday
126 Monday Tuesday Wednesday Thursday Friday Saturday
127 Sunday Monday Tuesday Wednesday Thursday Friday Saturday

Feb 20 '08 #4

P: n/a
MLH
Lemme see now... maybe a better way to demonstrate the
obstacle I'm facing would be ... hmmm???? OK, try this in
the immediate window:

?IIf((1 And 127)=1,"Sunday ","")
Sunday
or
?IIf((1 And 127)=1,"BlahBlahBlah","")
BlahBlahBlah

Now try to make a query field named [Published] that uses
the SAME identical syntax. Something like this pasted into
a query field should provide food for thought when you run it.

Published: IIf((1 And 127)=1,"BlahBlahBlah","")

You are not going to get the expected results. At least I don't.
Feb 20 '08 #5

P: n/a
MLH
Yep. You're right.
That's the smart way to do it.

It's only academic, but God I'd like to know why

?IIf((1 And 127)=1,"Sunday ","")
Sunday

works in the debug window but not in the QBE grid???
Feb 20 '08 #6

P: n/a
MLH
I don't have the WeekdayNames function, so I modified
your suggestion a bit and landed upon this...

Public Function PubDays(ByVal NPPubDay&, CallingProcedure As String)
As String
'************************************************* *************************
' Accepts a number, 1 to 127. Returns string represent days of
' the week on which a particular newspaper rag is published.
'
' Contributed by Lyle Fairfield, 2-19-08, with slight modifications.
'************************************************* *************************
On Error GoTo PubDays_Err
Dim PString As String

If (1 And NPPubDay) = 1 Then PString = "Sunday "
If (2 And NPPubDay) = 2 Then PString = PString & "Monday "
If (4 And NPPubDay) = 4 Then PString = PString & "Tuesday "
If (8 And NPPubDay) = 8 Then PString = PString & "Wednesday "
If (16 And NPPubDay) = 16 Then PString = PString & "Thursday "
If (32 And NPPubDay) = 32 Then PString = PString & "Friday "
If (64 And NPPubDay) = 64 Then PString = PString & "Saturday"
PubDays = Trim(PString)

PubDays_Exit:
Exit Function

PubDays_Err:
Dim r As String, z As String, Message3 As String
r = "The following unexpected error occurred in AppSpecific's
Function PubDays(), line #" & Trim$(CStr(Erl))
z = ", when called from " & CallingProcedure & ":" & CRLF & CRLF &
str$(Err) & ": " & Quote & Error$ & Quote
Message3 = r & z
MsgBox Message3, 48, "Unexpected Error - " & MyApp$ & ", rev. " &
MY_VERSION$
Resume PubDays_Exit

End Function

Feb 20 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.