Help with IF AND IF

I have a form that contains the following fields:
EmployeeID
OTDate
HoursPay
RateOfPay
TypeOfJob
Notes

I am trying to write a code that upon lost focus of the Notes field will check the TypeOfJob field to see if its value is "Vacation" and if so to check the HoursPay field to see if its value is greater than 8 and iff so to create a new record copying the data of the current records EmployeeID, OTDate, RateOfPay, and Notes fields and set the value of the TypeOfJob field to "112Vacation" and set the value of the HoursPay field to the remaining value over 8 hours that was originally set in the previous record.

For example:
If I enter the following in my form:
EmployeeID: 5555
OTDate:01/14/2009
HoursPay: 12
RateOfPay: Supervisor
TypeOfJob: Vacation
Notes: Smiths vac.

Then after exiting the Notes field a new record should be created as follows:
If I enter the following in my form:
EmployeeID: 5555
OTDate:01/14/2009
HoursPay: 4
RateOfPay: Supervisor
TypeOfJob: 112Vacation
Notes: Smiths vac.

Thanks
Dan

re: Help with IF AND IF

I would suggest something like a "Submit" button, so users know when they are triggering it, rather than on lost focus. Say they don't fill in the fields in order or something?

Some assumptions here that could change the syntax of the SQL statement:
EmployeeID is an Int
OTDate is a Date
HoursPay is an Int
The rest are Strings
Those are all the fields in your table, otherwise you would need to enumerate them before the Values in the Insert statement.

re: Help with IF AND IF

Thanks for the help I am a self taught novice here so please deal with me.

Employee ID is text
you are correct with your other assumptions.

Could you dumb it down for me a bit, could you give me an idea of what the If statement should look like?
Also in
< strTypeOfJob = "1" & HoursPay & "Vacation" 'Not sure where the "1" comes from
intHoursPay = HoursPay - 8 >

Is that the code I need to put in or are you aking me for clearification about the 1?
Thanks
Dan

re: Help with IF AND IF

In strTypeOfJob = "1" & HoursPay & "Vacation" that is the code if you just want to paste a 1 in front of the HoursPay and put "Vacation" after it. But yeah I wasn't sure exactly what you wanted there.

Well that wasn't as complicated as I thought when I first read your description, sorry.


If the EmployeeID is text, it will need extra " around it in the string. So you do this by putting in "". The new strSQL would be:
strSQL = "INSERT INTO myTableName VALUES (""" & EmployeeID _ & """, #" & OTDate & "#, " & intHoursPay & ", """ & RateOfPay _
& """, """ & strTypeOfJob & """, """ & Notes & """)"

and when it's evaluated it will be
INSERT INTO myTableName VALUES ("EmployeeID", #OTDate#, intHoursPay, "RateOfPay", "strTypeOfJob", "Notes")

re: Help with IF AND IF

Ok so far I have this but it is not correct because I think I confused you on the fields:
<Code>
1. If [TypeOfJob] = "Vacation" And [HoursPay] > 8 Then
2. strTypeOfJob = "1" & HoursPay & "Vacation"
3. intHoursPay = HoursPay - 8
4. strSQL = "INSERT INTO myTableName VALUES (""" & EmployeeID & """, #" & OTDate & "#, " & intHoursPay & ", """ & RateOfPay & """, """ & strTypeOfJob & """, """ & Notes & """)"
5. End If
<End Code>

Question:
What is the "1" in Line two
What do you mean by "and when it's evaluated it will be
INSERT INTO myTableName VALUES ("EmployeeID", #OTDate#, intHoursPay, "RateOfPay", "strTypeOfJob", "Notes")"? Where do I plug this in?

Thanks
Dan

re: Help with IF AND IF

The "1" in line 2 is because you wanted "112Vacation". I assumed the 12 was related to hours and the 1 was there for some other reason. But you can set strTypeOfJob = whatever you want using the same methods.

With the evaluated string, I was just trying to explain why you use the extra "". That is the string that will be executed when the code runs. Just put
in your code.

Sorry if I was confusing.

re: Help with IF AND IF

Okay here is what I have but I get a variable not defined error on strTypeOfJob in line 2

<Code>
1. If [TypeOfJob] = "Vacation" And [HoursPay] > 8 Then
2. strTypeOfJob = "112Vacation" & HoursPay & "Vacation" 'Not sure where the "1" comes from
3. intHoursPay = HoursPay - 8
4. strSQL = "INSERT INTO tblOvertime VALUES (""" & EmployeeID & """, #" & OTDate & "#, " & intHoursPay & ", """ & RateOfPay & """, """ & strTypeOfJob & """, """ & Notes & """)"
5. End If
<End Code>

Thanks Again
Dan

re: Help with IF AND IF

At the beginning of your code
Since we have to Dim all variables before we use them.
Do you always want to set the TypeOfJob = "112Vacation"? If so then just do The other stuff was because I thought it was variable.

re: Help with IF AND IF

Ok thanks now my code looks like the following and I am getting a variable not defined error on strSQL in line 6 how do I dim that one?

<Code>
1. Dim strTypeOfJob As String
2. Dim intHoursPay As Integer
3. If [TypeOfJob] = "Vacation" And [HoursPay] > 8 Then
4. strTypeOfJob = "112Vacation" & HoursPay & "Vacation"
5. intHoursPay = HoursPay - 8
6. strSQL = "INSERT INTO tblOvertime VALUES (""" & EmployeeID & """, #" & OTDate & "#, " & intHoursPay & ", """ & RateOfPay & """, """ & strTypeOfJob & """, """ & Notes & """)"
7. End If
<End Code>

re: Help with IF AND IF

strSQL is a string variable so at the top have
along with the other Dim statements.

re: Help with IF AND IF

Ok now here is my code and it does not work
I wanted it to create a new record with the data from the old with the cahnges but nothin happens I tried it with a button and with on lost focus but no new record is created. Why?

Dim strTypeOfJob As Integer
Dim strSQL As String
Dim intHoursPay As Integer
If [TypeOfJob] = "1" And [HoursPay] > 8 Then
strTypeOfJob = "99"
intHoursPay = [HoursPay] - 8
strSQL = "INSERT INTO tblOvertime VALUES ("" & EmployeeID & "", #" & OTDate & "#, " & intHoursPay & ", """ & RateOfPay & """, """ & strTypeOfJob & """, """ & Notes & """)"
End If

re: Help with IF AND IF

You still need the
right before the End If.
You put together the strSQL, but you didn't execute it to make any changes to the database.

re: Help with IF AND IF

Okay it is almost working, now I am getting a datatype mismatch error does anything pop out at you?

Dim strTypeOfJob As Integer
Dim strSQL As String
Dim intHoursPay As Integer
If [TypeOfJob] = "1" And [HoursPay] > 8 Then
strTypeOfJob = "99"
intHoursPay = [HoursPay] - 8
strSQL = "INSERT INTO tblOvertime VALUES (""" & EmployeeID & """, #" & OTDate & "#, " & intHoursPay & ", """ & RateOfPay & """, "" & strTypeOfJob & "", """ & Notes & ""","" & ID & "")"
CurrentDb.Execute strSQL, dbFailOnError
End If


My fields are as follows:
EmployeeID = integer
OTDate = Date
HoursPay = integer
RateOfPay = currency
TypeOfJob = integer
Notes = text
ID =autonumber


Thanks for all the help
Dan

re: Help with IF AND IF

Based on those datatypes, you need:
strSQL = "INSERT INTO tblOvertime VALUES (" & EmployeeID & ", #" & OTDate & "#, " & intHoursPay & ", " & RateOfPay & ", " & strTypeOfJob & ", """ & Notes & """," & ID & ")"

re: Help with IF AND IF

Based on those datatypes, you need:

re: Help with IF AND IF

Based on those datatypes, you need:
Sorry, forums didn't seem to be working.

re: Help with IF AND IF

Hi thanks but I am still getting the datatype mismatch could it be the id field that is an autonumber?
When I go to the highlighted section it lists dbFailOnError = 128

my code is:
<Code>
Dim strTypeOfJob As Integer
Dim strSQL As String
Dim intHoursPay As Integer
If [TypeOfJob] = "1" And [HoursPay] > 8 Then
strTypeOfJob = "99"
intHoursPay = [HoursPay] - 8
strSQL = "INSERT INTO tblOvertime VALUES (" & EmployeeID _
& ", #" & OTDate & "#, " & intHoursPay & ", " & RateOfPay & ", " _
& strTypeOfJob & ", """ & Notes & """," & ID & ")"
CurrentDb.Execute strSQL, dbFailOnError
End If
<End Code>

re: Help with IF AND IF

Try this, now that we know TypeOfJob is an Integer and not a String.
Change

Dim strTypeOfJob As Integer
to
Dim intTypeOfJob as Integer

strTypeOfJob = "99"
to
intTypeOfJob = 99

and in the strSQL = "...", change strTypeOfJob to intTypeOfJob

I believe that will do it.

re: Help with IF AND IF

I am now getting a Runtime error 3464: Data type mismatch in criteria expression:
CurrentDb.Execute strSQL, dbFailOnError

re: Help with IF AND IF

Any ideas on why it is returning this error?
Runtime error 3464: Data type mismatch in criteria expression:
CurrentDb.Execute strSQL, dbFailOnError

re: Help with IF AND IF

Can you paste your current code? Those are all of the fields in the table right?

re: Help with IF AND IF

Thanks my fields and their type are now as follows and yes there are no other fields then listed below:

EmployeeID - Text
OTDate - Date
HoursPay - Double
RateofPay - Currency
TypeOfJob - integer
Notes - text
ID - Autonumber

My Code is as follows:

<Code>
Dim intTypeOfJob As Integer
Dim strSQL As String
Dim dblHoursPay As Double
If [TypeOfJob] = 1 And [HoursPay] > 8 Then
intTypeOfJob = 99
dblHoursPay = [HoursPay] - 8
strSQL = "INSERT INTO tblOvertime VALUES (""" & EmployeeID _
& """, #" & OTDate & "#, " & dblHoursPay & ", " & RateOfPay & ", " _
& intTypeOfJob & ", """ & Notes & """," & ID & ")"
CurrentDb.Execute strSQL, dbFailOnError
End If
<End Code>

re: Help with IF AND IF

Last time listed the fields you had EmployeeID as Integer, so is this correct? The syntax looks ok to me if those are right, and you don't need to list the fieldnames before the values if you are putting something in each field.

re: Help with IF AND IF

Yes the employee field is asctualy text. Any ideas why I am getting the error?

re: Help with IF AND IF

You may have to put
MsgBox strSQL
right before the CurrentDB.Execute
and copy to here what it says in the message box when it comes up, because I can't tell what the problem could be. Sorry I just can't see anything wrong with the code.

re: Help with IF AND IF

Ok the msg box says:

Insert INTO tblOvertime VALUES ("0418", #2/1/2009#, 8, 35.748, 99, "", 2102)

re: Help with IF AND IF

Looks ok. Does it work if you put something in Notes by any chance?

re: Help with IF AND IF

I got it someone pointed out we were not puting it anywhere the code as follows now works. Thank you for all you help.

<Code>
Private Sub Notes_LostFocus()
Dim intTypeOfJob As Integer
Dim strSQL As String
Dim dblHoursPay As Double
If [TypeOfJob] = 1 And [HoursPay] > 8 Then
intTypeOfJob = 21
dblHoursPay = [HoursPay] - 8
strSQL = "INSERT INTO tblOvertime (EmployeeID, OTDate, HoursPay, " & _
"RateofPay, TypeOfJob, Notes) " & _
"VALUES (""" & EmployeeID & """, #" & OTDate & "#, " & dblHoursPay & _
", " & RateOfPay & ", " & intTypeOfJob & ", """ & Notes & """)"
CurrentDb.Execute strSQL, dbFailOnError
End If
End Sub
<End Code>

re: Help with IF AND IF

Glad it works. You know, I should have realize you probably can't insert an autonumber. Now I'll know if I use them. Thank you!