I need help with SQL please

[Afrosheen via AccessMonster.com]

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 & _
""" or EmpSuper.IntRec=""" & strMon & _
""" or EmpSuper.FinalRec=""" & 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'm 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 don't 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.

 

[Jeff Boyce]

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

"WHERE YourDateField = #" & [YourDateComparisonValue] & "#"

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.

 

[Daryl S]

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 & _
"# or EmpSuper.IntRec=#" & strMon & _
"# or EmpSuper.FinalRec=#" & strMon & "#"

 

[Douglas J. Steele]

<picky>
The others have given you correct answers, but be aware that they won't
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

"WHERE YourDateField = " & Format([YourDateComparisonValue],
"\#yyyy\-mm\-dd\#")
</picky>

 

[Afrosheen via AccessMonster.com]

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 don't need the group because of the
way I started the SQL.

<picky>
The others have given you correct answers, but be aware that they won't
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

"WHERE YourDateField = " & Format([YourDateComparisonValue],
"\#yyyy\-mm\-dd\#")

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

[quoted text clipped - 37 lines]

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

 

[Douglas J. Steele]

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!)

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 don't need the group because of
the
way I started the SQL.

<picky>
The others have given you correct answers, but be aware that they won't
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

"WHERE YourDateField = " & Format([YourDateComparisonValue],
"\#yyyy\-mm\-dd\#")

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

[quoted text clipped - 37 lines]

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

 

[AccessVandal via AccessMonster.com]

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's call
it "GroupPlan" and have it populate with a string "WorkRec" or "intRec" or
"FinalRec" or just the IDs number instead.

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

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

 

[AccessVandal via AccessMonster.com]

Note the error.

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

 

[Afrosheen via AccessMonster.com]

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's
based on the Workplan Rec. If there is a date then the report will show all
dates.

 

[Afrosheen via AccessMonster.com]

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

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

Do you possible need

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

Thanks for the reply. With this:

[quoted text clipped - 32 lines]

 

[AccessVandal via AccessMonster.com]

Hope Douglas J. Steele suggestion works for you.

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

1. Create a table let's call it "tblGroupPlan" with Fields "GPID" and
"GroupPlan". So you'll have something like

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
"EmpSuperDetail" with Fields "EmpSuperId", "GroupPlan" and "GroupPlanDate". (
assuming you know how to create a sub-form)

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

So you'll 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)

Thanks for getting back to me AccessVandal. The idea of one field [group plan]