I need help with SQL please

I have a form with a group box on it. The code for it is:

20    Select Case grpPlan.Value
Case 1
30        strMon = "WorkRec"
40      Case 2
50        strMon = "IntRec"
60      Case 3
70        strMon = "FinalRec"
80    End Select

After I select the one I want then the SQL is build based on the selection
and a report is generated.

strWhere = "EmpSuper.WorkRec=""" & strMon & _

30        stDocName = "rptTapsDueRec"
40        DoCmd.OpenReport stDocName, acPreview, , strWhere, , strMon

I know the group "strMon" is a string..

The thing is the actual fields are date fields.

What I am trying to accomplish is when I select the group it will give a
report with the dates that are in the field. Some of the records have them.
Some do not.

When I did the debug.print strWhere it showed:
EmpSuper.WorkRec="WorkRec" or EmpSuper.IntRec="WorkRec" or EmpSuper.
FinalRec="WorkRec"

In this case the strWhere is correct. I just do not know how to write the SQL
for dates.

When I ran the report it gave me a data type mismatch.

As always, thanks for your help and thanks for reading this post.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

If the fields in question are date/time datatype fields, you need

If the fields in question are date/time datatype fields, you need different
delimiters, something like (untested):


Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Afrosheen -If you want to use dates instead of strings, use the # delimeter

Afrosheen -

If you want to use dates instead of strings, use the # delimeter instead of
the " delimeter.  So if strMon is a date variable, then your strWhere would
be built like this:

strWhere = "EmpSuper.WorkRec=#" & strMon & _


--
Daryl S

<picky>The others have given you correct answers, but be aware that they will

The others have given you correct answers, but be aware that they will not
necessarily work for all users.

Access is very particular about the date format. Unless you can guarantee
that your users will NEVER have anything other than mm/dd/yyyy as their
Short Date format (through Regional Settings in the Control Panel), use


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Thanks for the reply. With this:20 Select Case grpPlan.

Thanks for the reply. With this:

20    Select Case grpPlan.Value
Case 1
30        strMon = "WorkRec"
40      Case 2
50        strMon = "IntRec"
60      Case 3
70        strMon = "FinalRec"
80    End Select

The strMon I guess are strings. But in actually they are field name that I
want to use.
So. in building the SQL what I want to do is if the fields have something in
them create and run the report. Maybe I do not need the group because of the
way I started the SQL.




--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

What do you mean "if the fields have something in them"?

What do you mean "if the fields have something in them"?

Do you possible need

strWhere = "EmpSuper." & strMon & " IS NOT NULL

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

I think you need to go back to the drawing board.

I think you need to go back to the drawing board. Having there fields/column
in a table tells me that the table is not normalize.

Why do you need three instead of one? Create a new Field/Column, let us call
it "GroupPlan" and have it populate with a string "WorkRec" or "intRec" or

The you just simply create the where clause for your string (remove the
quotes if it a number).

strWhere = "EmpSuper.WorkRec=" & """" & strMon & """"


--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

Note the error.strWhere = "EmpSuper.

Note the error.

strWhere = "EmpSuper.GroupPlan=" & """" & strMon & """"

--
Please Rate the posting if helps you.

Message posted via http://www.accessmonster.com

Thanks for getting back to me AccessVandal.

Thanks for getting back to me AccessVandal. The idea of one field [group plan]
sounds good. The problem is that my employer need all three fields. It is set
up like this and in this order.

Work Plan  Workplan Rec    Interim     Interim Rec        Final
FinalDue
January         02/01/2010       March      04/01/2010     December
12/31/2010

I already have a "strWhere" routine like the original one posted where I can
pull the months. What I was looking for is one that would find all fields
with dates.

I think John's code may work, I just need to test it out some more. it is
based on the Workplan Rec. If there is a date then the report will show all
dates.




--
Message posted via http://www.accessmonster.com

Hi Doug,I tested your code and it works great. Thanks another great job.

Hi Doug,
I tested your code and it works great. Thanks another great job.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

Hope Douglas J. Steele suggestion works for you.Here is I what think.

Hope Douglas J. Steele suggestion works for you.

Here is I what think. Watch for word wrap in your browser.

1. Create a table let us call it "tblGroupPlan" with Fields "GPID" and

GPID	GroupPlan
1	WorkPlan
2	WorkPlanRec
3	Interim
4	InterimRec
5	Final
6	FinalDue

Use this to auto-populate the field of a combobox "cboGroupPlan" in the sub-
form.

2. Create a Child Table (one to many) for EmpSuper. Let's call it
assuming you know how to create a sub-form)

Link your EmpSuper table "ID" to match the Child Table "EmpSuperId".

So you will have data like...

EmpSuperID	GroupPlan	                     GroupPlanDate
xxx1		WorkPlan	                     January
xxx1		WorkPlanRec	     02/01/2010
xxx1		Interim		     March
xxx1		InterimRec	                     04/01/2010
xxx1		Final		     December
xxx1		FinalDue	                     12/31/2010
xxx2		so on......

You can capture the "GroupPlan" datatype as a Number or a String.

strWhere = ..... EmpSuper.SomeID = EmpSuperDetail.EmpSuperID AND
EmpSuperDetail.GroupPlan = 1  AND EmpSuperDetail.GroupPlan = 2 AND Not IsNull
(EmpSuperDetail.GroupPlanDate)  ............so on..... (assuming GroupPlan is
datatype = number)


--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1