And or If Statement

Printable View

Nov 11th, 2007, 06:34 PM
Vincentd

And or If Statement

I need to have the following code state "0A" or "0B" (zero A or zero B). Please see the specific lines in bold.
Any help will greatly be appreciated.

If bRetirementNo0 = False And sRetirementNo < "50000000" Then
If lReportcode = "010" Then
StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo)
ElseIf lReportcode = "020" Then
 sRetirementNo = "0A" & Mid(sRetirementNo, 2) StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo)
End If

sSql = "INSERT INTO tl_PR_NYSRetirementReport "
sSql = sSql & "(fknCompany, fknEmployee, sName, sRetirementNumber, sSocialSecurityNumber, nTier, cGrossSalary )"
sSql = sSql & "SELECT DISTINCTROW "
sSql = sSql & "td_PREmployees.fknCompany, "
sSql = sSql & "AND ((td_PRCalculatedHistory.ynVoided)=False) "
sSql = sSql & "AND ((IIf(Not IsNull([sRetirementAccountNumber]) "
 sSql = sSql & "And Left$([sRetirementAccountNumber],2)='0A',True,False))"
If Me!opgReportType = 1 Then
sSql = sSql & "=False)) "
Else
sSql = sSql & "=True)) "
End If
Nov 11th, 2007, 07:25 PM
TBeck

Re: And or If Statement

what is it currently doing? and under what condtions does it need to say 0A and when does it need to say 0B?
and it would make things easier to read if you put some vb code tags around the code portion :)
Nov 11th, 2007, 08:12 PM
Vincentd

Re: And or If Statement

Sorry for the lack of tags.
The code is suppose to determine if an employee's retirement number begins with an "0A" and if it does, it's to be displayes on report code 20. If it doesn't, then it's to be displayed on report 10.
Both reports, by code number are exported to an ASCII file.
I was hoping to add some additional code that would instruct the program to include "0B" as well as "0A" and follow the same conditions described in the code.

Re: And or If Statement

i think i know what you mean... i'm just not sure where you are storing the 0A or B0
if you use something like

vb Code:

If lReportcode = "010" Then
StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo)
ElseIf lReportcode = "020" And Condition1 Then
sRetirementNo = "0A" & Mid(sRetirementNo, 2) StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo)
ElseIf lReportcode = "020" And Condition2 Then
sRetirementNo = "0B" & Mid(sRetirementNo, 2) StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo)
End If

and then replace Condition1 and Condition2 with the conditions for those instances when you want to output 0A and 0B respectivly

alternativly you can use nested if statements like this

vb Code:

If lReportcode = "010" Then
StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo)
ElseIf lReportcode = "020" Then
If Condition1 Then
sRetirementNo = "0A" & Mid(sRetirementNo, 2) StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo)
ElseIf Condition2 then 
sRetirementNo = "0B" & Mid(sRetirementNo, 2) StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo)
End If
End If

Nov 12th, 2007, 08:53 AM
Vincentd

1 Attachment(s)

Re: And or If Statement

I very much appreciate the suggested coding, but it did not work. I know that I need to provide more information which I hope to have done in the attachment (I tried uploading the form that contains the original code that includes one of your suggestions).
The two areas that contain the sorting are under 'Registration Number and 'Output to ASCII file
Thanks again for you efforts.
Nov 12th, 2007, 12:53 PM
TBeck

Re: And or If Statement

when I try to open the file in access it gives me a security warning and says it may be a macro virus, any ideas what would be causing that?
and also, since you are doing visual basic embedded in Access, there is a subforum for VB code in office applications development (http://www.vbforums.com/forumdisplay.php?f=37) so if we can't get your problem solved here, you could try there
Nov 12th, 2007, 01:52 PM
Vincentd

Re: And or If Statement

The security warning is generated by Access. Open Access - Tools - Macro - Security.
If your Macro security is set to Med or High, you'll get the warning message.
The form/code that I sent you has several macro references.....but no virus.
Nov 12th, 2007, 03:59 PM
TBeck

Re: And or If Statement

ok, i'll take a look when i get home... i have never really used access so I wasnt sure what the warning was about
Nov 27th, 2007, 07:43 PM
Vincentd

Re: And or If Statement

Sorry for the delay in following up. I've been trying to make your suggested code work, and have had some limited success. I need to ask what you mean by replacing Condition1 / Condition2 with the condition for those instances. Also, all values are store in temporary tables for use by two different reports. Appreciate any insight.
Nov 27th, 2007, 10:29 PM
TBeck

Re: And or If Statement

the conditon1 and condition2 need to be replaced with boolean statements, but I wasn't sure what the statements were. condition1 = conditions when to output 0A before it and condtion2 = conditions when to output 0B before it. If you do not know/cannot figgure out the boolean expression for these cases, let me know what the conditions are in words, and I can help you formulate it into a boolean expression
Nov 29th, 2007, 08:09 PM
Vincentd

Re: And or If Statement

I appreciate your continued help....and apologize for being so dense. I believe that the statement(s) you are looking for is contained in this section of code

HTML Code:

If bRetirementNo0 = False And sRetirementNo < "50000000" Then If lReportcode = "010" Then StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo) ElseIf lReportcode = "020" Then sRetirementNo = "0A" & Mid(sRetirementNo, 2) StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo) End If

The bRetirementNo0 is Dim as Boolean. Not sure it that helps. This section of code takes the retirement number entered on a form and places it on a hard copy report and then creates an ASCII file. The entire length of the entry is 8 digits. I've tried several renditions of statments, but have had limited success (meaning that I can only get 0A's or 0B's on one report, not both). I haven't a clue at this point as to how to make this thing happen.
Nov 29th, 2007, 08:22 PM
TBeck

Re: And or If Statement

ok i can see the contition for 0A now, but where do you give it 0B?
Nov 30th, 2007, 08:15 AM
Vincentd

Re: And or If Statement

I don't have any condition for 0B. This is something new that needs to be added to the code and resulting ACSII file.
Dec 2nd, 2007, 10:56 AM
Vincentd

Re: And or If Statement

I can't seem to create a new condition that allows the "0B" entry as well as the "0A". Any resulting reports from code changes that I make only reflect the "0A". Any help with creating the additional condition will greatly be appreciated.
Dec 2nd, 2007, 11:56 AM
MartinLiss

Re: And or If Statement

Are you coding this from within Access?
Dec 2nd, 2007, 02:40 PM
Vincentd

Re: And or If Statement

Yes. The code is behind a form.
Dec 2nd, 2007, 07:10 PM
MartinLiss

Re: And or If Statement

Okay then, I've moved the thread to this forum.
Dec 2nd, 2007, 09:16 PM
westconn1

Re: And or If Statement

how do you tell if the item is an OB item? or an OA item, does it have a different reportcode?
Dec 3rd, 2007, 09:04 AM
Vincentd

Re: And or If Statement

"0A" and/or "0B" belong to Report Code 20 only. Any other eight digit numbers belongs to Report Code 10. The user assigns the eight digit number when entering the employee's information. This eight digit number determines which report prints the respective employee's information.
Dec 3rd, 2007, 03:10 PM
westconn1

Re: And or If Statement

so if both 0A and 0B are reportcode 20, how can you tell which are 0A and which are 0B? you have give no explanation as to how you can identify each
Dec 3rd, 2007, 04:31 PM
Vincentd

Re: And or If Statement

The code now states that Report 20's registration number begins with "0A". Any other number belongs to Report 10. The code works as is. Now, I want registration number "0B" to belong to Report 20 along with "0A". I can't seem to code any conditions or "and,or" statements that will make this happen. What am I missing?
Dec 3rd, 2007, 09:16 PM
westconn1

Re: And or If Statement

Quote:

Now, I want registration number "0B" to belong to Report 20 along with "0A"

which variable holds the registration number?
as it looks like you are addng the 0A in your code, how do you determine if a item with reportcode 20 is 0A or 0B?

edit: this is what everyone asks.... how can you tell which is 0A or 0B?
Dec 4th, 2007, 11:01 AM
Vincentd

Re: And or If Statement

sRetirementNo contains the registration number. The code now states:

Code:

"Elseif IReportCode="020" then sRetirementNo="0A".

The user selects which report to generate by clicking on the Report 20 radio button located on a form. The system then looks at each employee to determine if their registration number begins with a "0A". If so, they will be listed on the report. If not, they belong to Report 10. This sceanario works fine. What I'm trying to do is add an additional variable "0B". If employee's registration number begins with "0B" (in additiona to "0A") they too should be reflected on Report 20.
Dec 4th, 2007, 03:11 PM
westconn1

Re: And or If Statement

Quote:

"Elseif IReportCode="020" then sRetirementNo="0A".

this code sets the sretirmentno to 0A if reportcode = 20, but from what you say above it looks as if you want to set the reportcode if the sretirementno contains 0A or 0B
Dec 4th, 2007, 04:44 PM
Vincentd

Re: And or If Statement

Correct!

Re: And or If Statement

try like this

vb Code:

If InStr(sRetirementNo, "0A") > 0 Or InStr(sRetirementNo, "0B") > 0 Then IReportCode = "020"

Dec 5th, 2007, 10:02 AM
Vincentd

Re: And or If Statement

Thank you for your time and the suggested code. I inserted your suggestion as follows but the resulting report did not reflex registration numbers that began with "0A" or "0B". The first two places of the registration code now reflects "00". Perhaps I placed your suggested code in the wrong place?

Code:

If bRetirementNo0 = False And sRetirementNo < "50000000" Then If lReportcode = "010" Then StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo) ElseIf lReportcode = "020" Then If InStr(sRetirementNo, "0A") > 0 Or InStr(sRetirementNo, "0B") > 0 Then lReportcode = "020" 'sRetirementNo = "0A" & Mid(sRetirementNo, 2) StrASCIIText = StrASCIIText & String(8 - Len(sRetirementNo), "0") & Trim(sRetirementNo) End If
Dec 5th, 2007, 03:14 PM
westconn1

Re: And or If Statement

your request and your previous code do not match, there is something that we can not see at all about what you are wanting to do

your previous code sets the "0A" into the string, and is done when reportcode = 20, it does not get the 0A from the retirement number, it looks to me as if you need to find where the 0B is stored so you can determine which records are 0B records
Dec 5th, 2007, 04:52 PM
Vincentd

1 Attachment(s)

Re: And or If Statement

I double checked and found that the registration number is indeed sRetirementNo. A statement in the coded states:

Code:

sRetirementNo = CheckNumeric(rsRetirement("sRetirementNumber"))

I've attached the form that contains all the code pertaining to my request. I "flaged" the specific section of code (to the left) with VINCENTD.
Dec 5th, 2007, 06:02 PM
Vincentd

Re: And or If Statement

To clarify further, the "OA" registration number, as well as the "0B" registration number are both entered by the user and stored in the same table. The entire registration number is eight digits long. The only difference between Report 10 and Report 20 is that the registration number for Report 20 begins with a "0A" (as shown in the code). I'm trying to add registration numbers that begin with "0B" for Report 20 as well.