Help with IF AND IF

[DAHMB]

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

[ChipR]

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?

dim strSQL as String
 
if blah blah blah
strTypeOfJob = "1" & HoursPay & "Vacation"  'Not sure where the "1" comes from
intHoursPay = HoursPay - 8
 
strSQL = "INSERT INTO myTableName VALUES (" & EmployeeID _
 & ", #" & OTDate & "#, " & intHoursPay & ", """ & RateOfPay _
 & """, """ & strTypeOfJob & """, """ & Notes & """)"
Currentdb.Execute strSQL, dbfailonerror 
 
End If
 

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.

[DAHMB]

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

[ChipR]

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.

 
If TypeOfJob = "Vacation" and HoursPay > 8 then
...
End If
 

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

[DAHMB]

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

[ChipR]

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

 strSQL = "INSERT INTO myTableName VALUES (""" & EmployeeID _
 & """, #" & OTDate & "#, " & intHoursPay & ", """ & RateOfPay _
 & """, """ & strTypeOfJob & """, """ & Notes & """)"

in your code.

Sorry if I was confusing.

[DAHMB]

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

[ChipR]

At the beginning of your code

Dim strTypeOfJob as String
Dim intHoursPay as Integer

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

strTypeOfJob = "112Vacation"

The other stuff was because I thought it was variable.

[DAHMB]

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>

[ChipR]

strSQL is a string variable so at the top have

Dim strSQL as String

along with the other Dim statements.

[DAHMB]

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

[ChipR]

You still need the

Currentdb.Execute strSQL, dbfailonerror

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

[DAHMB]

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

[ChipR]

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

[ChipR]

Based on those datatypes, you need:

strSQL = "INSERT INTO tblOvertime VALUES (" & EmployeeID _
 & ", #" & OTDate & "#, " & intHoursPay & ", " & RateOfPay _
& ", " & strTypeOfJob & ", """ & Notes & """," & ID & ")"

[ChipR]

Based on those datatypes, you need:

strSQL = "INSERT INTO tblOvertime VALUES (" & EmployeeID _
 & ", #" & OTDate & "#, " & intHoursPay & ", " & RateOfPay & ", " _
 & strTypeOfJob & ", """ & Notes & """," & ID & ")"

Sorry, forums didn't seem to be working.

[DAHMB]

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>

[ChipR]

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.

[DAHMB]

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

[DAHMB]

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

[ChipR]

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

[DAHMB]

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>

[ChipR]

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.

[DAHMB]

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

[ChipR]

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.

[DAHMB]

Ok the msg box says:

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

[ChipR]

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

[DAHMB]

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>

[ChipR]

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!