By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,484 Members | 1,764 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,484 IT Pros & Developers. It's quick & easy.

Help with IF AND IF

100+
P: 147
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
Jan 14 '09 #1
Share this Question
Share on Google+
28 Replies


Expert 100+
P: 1,287
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?

Expand|Select|Wrap|Line Numbers
  1. dim strSQL as String
  2.  
  3. if blah blah blah
  4. strTypeOfJob = "1" & HoursPay & "Vacation"  'Not sure where the "1" comes from
  5. intHoursPay = HoursPay - 8
  6.  
  7. strSQL = "INSERT INTO myTableName VALUES (" & EmployeeID _
  8.  & ", #" & OTDate & "#, " & intHoursPay & ", """ & RateOfPay _
  9.  & """, """ & strTypeOfJob & """, """ & Notes & """)"
  10. Currentdb.Execute strSQL, dbfailonerror 
  11.  
  12. End If
  13.  
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.
Jan 14 '09 #2

100+
P: 147
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
Jan 14 '09 #3

Expert 100+
P: 1,287
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. If TypeOfJob = "Vacation" and HoursPay > 8 then
  3. ...
  4. End If
  5.  
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")
Jan 14 '09 #4

100+
P: 147
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
Jan 14 '09 #5

Expert 100+
P: 1,287
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
Expand|Select|Wrap|Line Numbers
  1.  strSQL = "INSERT INTO myTableName VALUES (""" & EmployeeID _
  2.  & """, #" & OTDate & "#, " & intHoursPay & ", """ & RateOfPay _
  3.  & """, """ & strTypeOfJob & """, """ & Notes & """)"
in your code.

Sorry if I was confusing.
Jan 14 '09 #6

100+
P: 147
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
Jan 14 '09 #7

Expert 100+
P: 1,287
At the beginning of your code
Expand|Select|Wrap|Line Numbers
  1. Dim strTypeOfJob as String
  2. 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
Expand|Select|Wrap|Line Numbers
  1. strTypeOfJob = "112Vacation"
The other stuff was because I thought it was variable.
Jan 14 '09 #8

100+
P: 147
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>
Jan 14 '09 #9

Expert 100+
P: 1,287
strSQL is a string variable so at the top have
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
along with the other Dim statements.
Jan 14 '09 #10

100+
P: 147
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
Jan 14 '09 #11

Expert 100+
P: 1,287
You still need the
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 14 '09 #12

100+
P: 147
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
Jan 14 '09 #13

Expert 100+
P: 1,287
Based on those datatypes, you need:
strSQL = "INSERT INTO tblOvertime VALUES (" & EmployeeID & ", #" & OTDate & "#, " & intHoursPay & ", " & RateOfPay & ", " & strTypeOfJob & ", """ & Notes & """," & ID & ")"
Jan 14 '09 #14

Expert 100+
P: 1,287
Based on those datatypes, you need:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO tblOvertime VALUES (" & EmployeeID _
  2.  & ", #" & OTDate & "#, " & intHoursPay & ", " & RateOfPay _
  3. & ", " & strTypeOfJob & ", """ & Notes & """," & ID & ")"
Jan 14 '09 #15

Expert 100+
P: 1,287
Based on those datatypes, you need:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO tblOvertime VALUES (" & EmployeeID _
  2.  & ", #" & OTDate & "#, " & intHoursPay & ", " & RateOfPay & ", " _
  3.  & strTypeOfJob & ", """ & Notes & """," & ID & ")"
Sorry, forums didn't seem to be working.
Jan 14 '09 #16

100+
P: 147
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>
Jan 15 '09 #17

Expert 100+
P: 1,287
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.
Jan 15 '09 #18

100+
P: 147
I am now getting a Runtime error 3464: Data type mismatch in criteria expression:
CurrentDb.Execute strSQL, dbFailOnError
Jan 15 '09 #19

100+
P: 147
Any ideas on why it is returning this error?
Runtime error 3464: Data type mismatch in criteria expression:
CurrentDb.Execute strSQL, dbFailOnError
Jan 15 '09 #20

Expert 100+
P: 1,287
Can you paste your current code? Those are all of the fields in the table right?
Jan 15 '09 #21

100+
P: 147
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>
Jan 15 '09 #22

Expert 100+
P: 1,287
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.
Jan 15 '09 #23

100+
P: 147
Yes the employee field is asctualy text. Any ideas why I am getting the error?
Jan 15 '09 #24

Expert 100+
P: 1,287
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.
Jan 15 '09 #25

100+
P: 147
Ok the msg box says:

Insert INTO tblOvertime VALUES ("0418", #2/1/2009#, 8, 35.748, 99, "", 2102)
Jan 15 '09 #26

Expert 100+
P: 1,287
Looks ok. Does it work if you put something in Notes by any chance?
Jan 15 '09 #27

100+
P: 147
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>
Jan 15 '09 #28

Expert 100+
P: 1,287
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!
Jan 15 '09 #29

Post your reply

Sign in to post your reply or Sign up for a free account.