SQL question on my form

[Afrosheen via AccessMonster.com]

Good morning. I'm having a little problem that I hope you can help me get
straight. I thought I had it correct but when I put breaks in it doesn't
display correctly. The information is taken from a Group box
This is the short code

120 Select Case grpDept.Value
240 Case 1
250 strCnt = "Ops. B-Nights"
260 Case 2
270 strDept = "Programs"
280 Case 3
290 strDept = "Programs/Chaplin"
300 Case 4
370 strDept = "Support Services"
380 Case 5
390 strDept = "Unit-1"
400 Case 6
410 strDept = "Unit-2"
420 Case 7
430 strDept = "Unit-3"
440 Case 8
450 strDept = "Unit-4"

480 End Select

The case statements work. Where I'm having the problem is for example in
stead of the person working in Unit-3, he is now working in Unit-3 A-Days.
Unit-3 has 4 different shifts [Days, Nights, A rotation and B rotation]. So
what I'm trying to do is to build the query like below, but it gives me just
the people in Unit-3 and not all the other.

It is on the "Where" statement is. Am I missing something?

490 cboloc.RowSource = "Select [lname] & ', '& [fname], , [email1],
[location]" & _
"From EmpSuper " & _
"Where location like '" & strDept & "' " & _
"And (((Email1) = true))" & _
"Order by Location;"

Your help is very much appreciated. Thank you.

 

[Douglas J. Steele]

Are you saying that the name of the department is Unit-3 A-Days, but that
all you're seeing are details for department Unit-3?

Change the bit of code

"Where location like '" & strDept & "' " & _

to

"Where location like '" & strDept & "*' " & _

 

[Daryl S]

Afrosheen -

If you need to see all locations that START WITH Unit-3, then you need an
asterisk in your Like construct. Try this:

cboloc.RowSource = "Select [lname] & ', '& [fname], , [email1],
[location]" & _
"From EmpSuper " & _
"Where location like '" & strDept & "*' " & _
"And (((Email1) = true))" & _
"Order by Location;"

Otherwise, please tell us what is not working - what are you getting and
what do you want to get.

 

[Afrosheen via AccessMonster.com]

The Daryl and Doug for writing back. I copied and pasted this code and it
appears not to work.

Here's more detail.
In the group box I have Administration, Unit-1, Unit-2, Unit-3 and Unit-4 and
more

Joe may work in Unit-3 A-Days
Bill may work in Unit-3 B-Days
Fred may work in Unit-3
Jim may work in Administration

What I need to do is to create a query or filter where if I select Unit-3
from the group box it will give me everyone in Unit-3

If I open up the form in design mode and click on the data tab for the query
it shows in the [location] field "like 'Unit-3*', but it does not display
everyone from Unit-3. It would just show Fred


I hope this helped.

Afrosheen -

If you need to see all locations that START WITH Unit-3, then you need an
asterisk in your Like construct. Try this:

cboloc.RowSource = "Select [lname] & ', '& [fname], , [email1],
[location]" & _
"From EmpSuper " & _
"Where location like '" & strDept & "*' " & _
"And (((Email1) = true))" & _
"Order by Location;"

Otherwise, please tell us what is not working - what are you getting and
what do you want to get.
[QUOTE]
Good morning. I'm having a little problem that I hope you can help me get
straight. I thought I had it correct but when I put breaks in it doesn't[/QUOTE]
[quoted text clipped - 37 lines][QUOTE]
Your help is very much appreciated. Thank you.[/QUOTE][/QUOTE]

 

[Daryl S]

Afrosheen -

Post the SQL for the query that is the record source of your form so we can
be sure that is correct. Also, post a list of all the unique values of
[location] in the table with the user data. This should include Unit-1,
Unit-3, Unit-3 A-Days, Unit-3 B-Days, and others. Then maybe we can spot the
issue.

--
Daryl S

The Daryl and Doug for writing back. I copied and pasted this code and it
appears not to work.

Here's more detail.
In the group box I have Administration, Unit-1, Unit-2, Unit-3 and Unit-4 and
more

Joe may work in Unit-3 A-Days
Bill may work in Unit-3 B-Days
Fred may work in Unit-3
Jim may work in Administration

What I need to do is to create a query or filter where if I select Unit-3
from the group box it will give me everyone in Unit-3

If I open up the form in design mode and click on the data tab for the query
it shows in the [location] field "like 'Unit-3*', but it does not display
everyone from Unit-3. It would just show Fred


I hope this helped.

Afrosheen -

If you need to see all locations that START WITH Unit-3, then you need an
asterisk in your Like construct. Try this:

cboloc.RowSource = "Select [lname] & ', '& [fname], , [email1],
[location]" & _
"From EmpSuper " & _
"Where location like '" & strDept & "*' " & _
"And (((Email1) = true))" & _
"Order by Location;"

Otherwise, please tell us what is not working - what are you getting and
what do you want to get.
[QUOTE]
Good morning. I'm having a little problem that I hope you can help me get
straight. I thought I had it correct but when I put breaks in it doesn't[/QUOTE]
[quoted text clipped - 37 lines][QUOTE]
Your help is very much appreciated. Thank you.[/QUOTE][/QUOTE]

--

http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201004/1

.
[/QUOTE]

 

[Afrosheen via AccessMonster.com]

This is the complete routine that is the selection from the group box
130 Select Case grpDept.Value
Case 1
140 strDept = "Administrative"
150 Case 2
160 strDept = "Food Service"
170 Case 3
180 strDept = "Maintenance"
190 Case 4
200 strDept = "Ops. A-Days"
210 Case 5
220 strDept = "Ops. A-Nights"
230 Case 6
240 strDept = "Ops. B-Days"
250 Case 7
260 strCnt = "Ops. B-Nights"
270 Case 8
280 strDept = "Programs"
290 Case 9
300 strDept = "Programs/Chaplin"
310 Case 10
320 strDept = "Programs/Dental"
330 Case 11
340 strDept = "Programs/Medical"
350 Case 12
360 strDept = "Programs/Psy."
370 Case 13
380 strDept = "Support Services"
390 Case 14
400 strDept = "Unit-1"
410 Case 15
420 strDept = "Unit-2"
430 Case 16
440 strDept = "Unit-3"
450 Case 17
460 strDept = "Unit-4"
470 Case 18
480 strDept = "Visition"
490 End Select


'strWhere = "[lname] Like '" & Me!txtSearch & "' " 'Find with Wild Card.



500 cboloc.RowSource = "Select [lname] & ', '& [fname], , [email1],
[location]" & _
"From EmpSuper " & _
"Where location like '" & strDept & "*' " & _
"And (((Email1) = true))" & _
"Order by Location;"

This information is the way it is in the query

The field name is called [location]
Administrative
Food Service
Maintenance
Not Placed
Ops. A-Days
Ops. A-Nights
Ops. B-Days
Ops. B-Nights
Programs
Programs/Dental
Programs/Medical
Programs/Psy.
Support Services
U-1 A-Days
U-1 A-Nights
U-1 B-Days
U-1 B-Nights
U-2 A-Days
U-2 A-Nights
U-2 B-Days
U-2 B-Nights
U-3 A-Days
U-3 A-Nights
U-3 B-Days
U-3 B-Nights
U-4 A-Days
U-4 A-Nights
U-4 B-Days
U-4 B-Nights
Unit-1
Unit-2
Unit-3
Unit-4
Visitation


I hope this is what you wanted.
[QUOTE="Afrosheen"]
The Daryl and Doug for writing back. I copied and pasted this code and it
appears not to work.

Here's more detail.
In the group box I have Administration, Unit-1, Unit-2, Unit-3 and Unit-4 and
more

Joe may work in Unit-3 A-Days
Bill may work in Unit-3 B-Days
Fred may work in Unit-3
Jim may work in Administration

What I need to do is to create a query or filter where if I select Unit-3
from the group box it will give me everyone in Unit-3

If I open up the form in design mode and click on the data tab for the query
it shows in the [location] field "like 'Unit-3*', but it does not display
everyone from Unit-3. It would just show Fred

I hope this helped.
[QUOTE]
Afrosheen -
[/QUOTE]
[quoted text clipped - 16 lines][QUOTE][/QUOTE][/QUOTE]

 

[Afrosheen via AccessMonster.com]

If you're still here, I tried to build a dummy query and tried the - like
"Unit-2*" - and it still didn't work.

This is the complete routine that is the selection from the group box
130 Select Case grpDept.Value
Case 1
140 strDept = "Administrative"
150 Case 2
160 strDept = "Food Service"
170 Case 3
180 strDept = "Maintenance"
190 Case 4
200 strDept = "Ops. A-Days"
210 Case 5
220 strDept = "Ops. A-Nights"
230 Case 6
240 strDept = "Ops. B-Days"
250 Case 7
260 strCnt = "Ops. B-Nights"
270 Case 8
280 strDept = "Programs"
290 Case 9
300 strDept = "Programs/Chaplin"
310 Case 10
320 strDept = "Programs/Dental"
330 Case 11
340 strDept = "Programs/Medical"
350 Case 12
360 strDept = "Programs/Psy."
370 Case 13
380 strDept = "Support Services"
390 Case 14
400 strDept = "Unit-1"
410 Case 15
420 strDept = "Unit-2"
430 Case 16
440 strDept = "Unit-3"
450 Case 17
460 strDept = "Unit-4"
470 Case 18
480 strDept = "Visition"
490 End Select

'strWhere = "[lname] Like '" & Me!txtSearch & "' " 'Find with Wild Card.


500 cboloc.RowSource = "Select [lname] & ', '& [fname], , [email1],
[location]" & _
"From EmpSuper " & _
"Where location like '" & strDept & "*' " & _
"And (((Email1) = true))" & _
"Order by Location;"

This information is the way it is in the query

The field name is called [location]
Administrative
Food Service
Maintenance
Not Placed
Ops. A-Days
Ops. A-Nights
Ops. B-Days
Ops. B-Nights
Programs
Programs/Dental
Programs/Medical
Programs/Psy.
Support Services
U-1 A-Days
U-1 A-Nights
U-1 B-Days
U-1 B-Nights
U-2 A-Days
U-2 A-Nights
U-2 B-Days
U-2 B-Nights
U-3 A-Days
U-3 A-Nights
U-3 B-Days
U-3 B-Nights
U-4 A-Days
U-4 A-Nights
U-4 B-Days
U-4 B-Nights
Unit-1
Unit-2
Unit-3
Unit-4
Visitation

I hope this is what you wanted.[QUOTE]
The Daryl and Doug for writing back. I copied and pasted this code and it
appears not to work.[/QUOTE]
[quoted text clipped - 22 lines][QUOTE][/QUOTE][/QUOTE]

 

[Afrosheen via AccessMonster.com]

First of all I found the problem. It was a mistake on my part and I want to
apologize for taking up your time, talent, and board space.

Where the problem was is where I was selecting Unit-3 in the case statement,
in the table it was U-3

I thank you for your help..

Afrosheen -

Post the SQL for the query that is the record source of your form so we can
be sure that is correct. Also, post a list of all the unique values of
[location] in the table with the user data. This should include Unit-1,
Unit-3, Unit-3 A-Days, Unit-3 B-Days, and others. Then maybe we can spot the
issue.

The Daryl and Doug for writing back. I copied and pasted this code and it
appears not to work.

[quoted text clipped - 37 lines]

 

[Daryl S]

Great! Glad you got it working...

--
Daryl S

First of all I found the problem. It was a mistake on my part and I want to
apologize for taking up your time, talent, and board space.

Where the problem was is where I was selecting Unit-3 in the case statement,
in the table it was U-3

I thank you for your help..

Afrosheen -

Post the SQL for the query that is the record source of your form so we can
be sure that is correct. Also, post a list of all the unique values of
[location] in the table with the user data. This should include Unit-1,
Unit-3, Unit-3 A-Days, Unit-3 B-Days, and others. Then maybe we can spot the
issue.

The Daryl and Doug for writing back. I copied and pasted this code and it
appears not to work.

[quoted text clipped - 37 lines]

Your help is very much appreciated. Thank you.

--



.