I am trying to use Insert Statement which is one of the field(format = Number) in Access = 0. My program does not understand data which is one of the field = 0. Can you help me to solve the problem.
instead of making people download a file, virus scan it, un-zip it, guess at the file, guess wrong, guess at another file, open it, guess where in the code it is, get lost, get frustrated and give up.... how about just posting the code in question?
I'd also suggest putting [code][/code] tags around it so that it keeps the format...
I am trying to use Insert Statement which is one of the field(format = Number) in Access = 0.
Example : Excel have field : Diem = 0 . Table SV (Diem(Value: Number)) = NULL
My program does not understand field Diem = 0 and it is not running.
Can you help me to solve the problem.
I am appreciate for your help !!!!!!!
Last edited by thedeathnotes210x; Nov 14th, 2012 at 09:43 PM.
Well, I'm running your really messed up code...you have a do: ??? loop out of order, missing two "end if"s and an if without a 'then'. Why don't you first, clean up your code, and just simply try to do ONE insert into your table without all the if statements. THEN, maybe I can help you find out why you can't insert. But until then, I'm outa here..that code is really, really confusing.
Dear SamOscarBrown,
I want to create the variable with the field Diem(value = Number) in Access as equal to ws.Range("F") because my program does not understand the line which is contain Diem = NULL.
My Excel have 7 field
MSSV MaLop TenLop Ho Ten Diem HanhKiem
1007 TH07 A Dung Nam Kha
As you know, Diem = NULL and the program could not Insert it because Diem (value = Number) is require Number and could not = 0. So I have to write the If Statement in order to the program is understand if Diem = NULL, the program insert normally.
Did you use the debug tool to follow the IF statement? Does it assign the approprate value to diem? If Diem is a number field in your database table, you will have to ensure your sql statement is written to insert the proper 'value' for Diem. It appears your sql above is ALWAYS attempting to insert a negative one (-1) for the Diem value....that is not what you want...you need to replace that value with your VARIABLE (which you need to declare (dimension) (integer, single or double)) Diem. (e.g. & "'" & Diem "'" & , instead of & "-1" &).
As I believe I have told you before (I mention this a lot to those use use 'con.execute'), it is always good practice when testing code to use a recordset object when inserting into/updating tables....then, it is easy to do a msgbox of the sql before executing it...gives you a bird's eye view of all the field names and values you are attempting to insert/update. By looking at the msgbox string, you should readily see if you have numerics which should be strings, dates which should have #'s, and so forth.
AND, when I insert Excel data into an Access table, I ALWAYS use a variable for each cell....some programmers probably do not, but it always helps me to see what I am about to insert. For beginners, I definitely recommend it...for those who are experienced and can quickly recognize numerics, dates and strings, then maybe not so important.
BUT, back to my first question...WHAT is not working, and what error(s) are you getting?
Diem in the database have value = Number. I insert Excel data into an Access table and I get the error "Data type mismatch" because Diem = Null. I am trying to write If Statement but If Statement is not running.
Diem in the database have value = Number. I insert Excel data into an Access table and I get the error "Data type mismatch" because Diem = Null. I am trying to write If Statement but If Statement is not running.
Alright, I see am getting nowhere fast....here is how to insert a number into a table: (Diem field is a numeric field, and myDiem is a number (in this case, an integer).
(assume cnn already dimensioned and connection established)
Code:
dim mySQL as string
dim MyDate as Date
dim myDiem as integer
myDate = Date
myDiem = 4
mySQL = "insert into test (MSSV, Ho, Malop, Ten, theUser, status, dateUpdated, Diem) values ('SAMMI', 'TEST', 'John', 'JONES', 'SAM','1',"
mySQL = mySQL & "#" & myDate & "#," 'I broke this apart so you'd see how to enter a DATE
mySQL = mySQL & Str(myDiem) & ")" 'and this I separated so you can see how to enter a NUMBER
cnn.Execute mySQL
In your case, use an IF statement to set Diem = zero if Diem is NULL before you run the sql. THEN, use DEBUG to look at all values in your SQL string.
[RESOLVED] Insert data = 0
Reply With Quote
