Need Help with IF Statements 2

[HJessen]

Could you PLEASE take 5 minutes to look at this code? I can not get the IF statements to execute.

If "[Data-Basic.SSN]" Then
stLinkCriteria = "E_Month ='" & Me![Month] & "' and " & "[E_Year] = '2003'"
Else:
If "[Month]" Is Null Then
stLinkCriteria = "[DATA-Basic].SSN ='" & Me![Name] & "' and " & "[E_Year] = '2003'"
Else:
stLinkCriteria = "[DATA-Basic].SSN ='" & Me![Name] & "' and " & "[E_Month] = '" & Me!Month & "'"
End If
End If

The system just passes over the if statements, or if I try to change something such as
If "[Month]" Is Null Then to If "[Month] = ' ' " Then

...the system tells me there is a type mismatch. I am lost here. This is the first time I have tried to do a combined search from two combo boxes, and run a report from it.


THANKS!
H. Jessen
"Now I know more, and I feel dummer"

 

[SgtJarrow]

First...when checking for Null fields....you should use:

If IsNull([field]) Then
instead of
If [field] Is null Then

Second...If you pasted your code from your module the first If line is wrong.....

If "[Data-Basic.SSN]" Then
should be
If Data-Basic.[SSN] Then

Finally, if you nee to check for an EMPTY field you should use:

If [field] = "" Then
and not
If [field] = ' '" Then

Just my two cents...spend em as you like. Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[VicM]

HJessen:

Is DATA-Basic.SSN and recordset/field identifier? If so, you don't need the quote marks (&quot around it. By using the quote marks you are testing the literal [DATA-Basic.SSN] and not what is in the field.

The same goes for [Month]. Also, I believe the correct syntax when testing for null is: IsNull([Month]).

Additionally, you refer to the variable/field as [DATA-Basic.SSN] and [DATA-Basic].SSN. These mean 2 differnt things to Access.

Vic

Are the values of [Month] and [E_Year], in the underlying recordset, numbers or text? If they are numbers then you do not need to enclose them in single quotes ('). Doing so tells Access to test [Month] and [E_Year] as literals.

But the thing that puzzles me the most is your use of Me. If this is a report as opposed to a form, then there are no active controls which can be addressed using the Me object.

I'd like to see a little more detail in what you are trying to do and the structure of the recordset. Not sure the points I've made above are really going to help you.

Vic

 

[HJessen]

TO: mstrmage1768

Tried your suggestion - still get problems. The current code is:

==========
If IsNull("Data-Basic.[SSN]&quot Then
stLinkCriteria = "[E_Month] = '" & Me![Month] & "' and " & "[E_Year] = '2003'"
Else:
' THIS IF STATEMENT WORKS!
If IsNull(Me![Month]) Then
stLinkCriteria = "[DATA-Basic].SSN ='" & Me![Name] & "' and " & "[E_Year] = '2003'"
Else:
' Following line works for both individual name and month being selected.
stLinkCriteria = "[DATA-Basic].SSN ='" & Me![Name] & "' and " & "[E_Month] = '" & Me!Month & "' and " & "[E_Year] = '2003'"
End If
End If
=============

My basic premise is that I want to be able to
(1) select an individual's name only
(2) select a particular month only
(3) select both an individual and a particular month

I can do 1 and 3, but can't get 2 to run.

Does this help any.

Also, where can I find more information on this type of
coding issue? I am lost as to when to use Me! or []
or "[]" or just "xxx".

THANK YOU for your help!!
H. Jessen
"Now I know more, and I feel dummer"

 

[HJessen]

THANK YOU ALL!!

I got it to work! However, the problem was on my side. I was using the work field name. OOPS!!!!

You ALL were a great help!
H. Jessen
"Now I know more, and I feel dummer"