VB question

**NeoPa** · Jul 24 '07, 05:16 PM

Originally posted by JKing

Seems you did end up jumping the gun there NeoPa :P. I do my best to get all info I can before trying to solve the error.

I'm sorry JKing, I really don't get what you mean. I don't feel insulted or anything, it just doesn't make sense to me. I haven't posted my reply yet as I'm waiting for the info.
You have, on the other hand :confused:
Is it a form of irony you're using? Excuse me if I appear dim.

**Stang02GT** · Jul 24 '07, 05:23 PM

Originally posted by NeoPa

I'm sorry JKing, I really don't get what you mean. I don't feel insulted or anything, it just doesn't make sense to me. I haven't posted my reply yet as I'm waiting for the info.
You have, on the other hand :confused:
Is it a form of irony you're using? Excuse me if I appear dim.

Neo,

I read the previous posts and i apologize but im not sure what you are asking for?

**JKing** · Jul 24 '07, 05:49 PM

Originally posted by NeoPa

I'm sorry JKing, I really don't get what you mean. I don't feel insulted or anything, it just doesn't make sense to me. I haven't posted my reply yet as I'm waiting for the info.
You have, on the other hand :confused:
Is it a form of irony you're using? Excuse me if I appear dim.

Sorry, NeoPa I was just poking fun at the fact that you had asked about the data types when I had already done the same previously. So by jumping the gun I meant assuming one thing had not already been stated. We were both thinking along the same lines which is good. I didn't mean to confuse you nor insult. Just thought it was kind of funny, though my sense of humor may be a little off from lack of sleep. Now I see that I am the one who has jumped the gun and I think that's funny too.

Nevertheless! I do believe nulls are the issue here.

**FishVal** · Jul 24 '07, 05:56 PM

Plz provide types of Tracker table fields. I mean open the table in design view and check types of table fields .
Despite you've posted in #20

Originally posted by Stang02GT

X5 and Z6 are both number fields

at least Tracker.Z6 is text field according to SQL statement generated by Query Builder (post #7)

Originally posted by Stang02GT

Ok i have made an append query and gotten the data into the table where it is supposed to go... here is my sql statement

Code:

INSERT INTO SRMisc ( [Tracker Item], Description, Comments, Requestor, [SR Num] )
SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6
FROM Tracker
WHERE (((Tracker.Z6)="3918"));

**Stang02GT** · Jul 24 '07, 06:01 PM

Originally posted by FishVal

Plz provide types of Tracker table fields. I mean open the table in design view and check types of table fields .
Despite you've posted in #20

at least Tracker.Z6 is text field according to SQL statement generated by Query Builder (post #7)

Then that is probably what is causing this error... if Z6 according to the SQL statement is a text feild then thats where i am getting hung up because both Z6 and X5 are Numeric Fields in the table.

If nulls are my problem, then like i said earlier that not every record has the Z6 data.....

what if i changed the order of the statment and switched X5 and Z6 in the statement so it looked like this

Code:

 "FROM Tracker WHERE Tracker.Z6=" & Me.Z6 & " OR Tracker.X5=" & Me.X5 & ";"

to this

Code:

 "FROM Tracker WHERE Tracker.X5=" & MeX5 & " OR Tracker.Z6=" & Me.Z6 & ";"

**FishVal** · Jul 24 '07, 06:11 PM

Originally posted by Stang02GT

Then that is probably what is causing this error... if Z6 according to the SQL statement is a text feild then thats where i am getting hung up because both Z6 and X5 are Numeric Fields in the table.

I suggest you to build append query with multiple criteria in Query Builder, make sure it is working properly and post SQL statement generated.

**Stang02GT** · Jul 24 '07, 06:13 PM

Originally posted by FishVal

I suggest you to build append query with multiple criteria in Query Builder, make sure it is working properly and post SQL statement generated.

Thats what i did the 1st time to get the current SQL statment that i have now...the only thing different about this one is the OR clause...but i will try it again

**NeoPa** · Jul 24 '07, 06:17 PM

Originally posted by JKing

Sorry, NeoPa I was just poking fun at the fact that you had asked about the data types when I had already done the same previously. So by jumping the gun I meant assuming one thing had not already been stated. We were both thinking along the same lines which is good. I didn't mean to confuse you nor insult. Just thought it was kind of funny, though my sense of humor may be a little off from lack of sleep. Now I see that I am the one who has jumped the gun and I think that's funny too.

Nevertheless! I do believe nulls are the issue here.

I agree with you.
EXACTLY how this manifests (and hence the best way to handle it) is something we can only know when Stang refers back to post #30, which refers back to post #26, which asks for (and says how to get) the SQL which causes the error message.
Here's hoping :D

**Stang02GT** · Jul 24 '07, 06:34 PM

Originally posted by NeoPa

I agree with you.
EXACTLY how this manifests (and hence the best way to handle it) is something we can only know when Stang refers back to post #30, which refers back to post #26, which asks for (and says how to get) the SQL which causes the error message.
Here's hoping :D

I have revisited post 26 which then refers me to another post...which then refers me to another post lol after all that has gone on today for me my head is spinning right now and trying to follow the trail of suggestions of revisiting posts i have no idea what i am supposed to be posting for you guys to look at.

i've posted my code numerous time (Neo you know all about that lol ), the error message, and the original SQL statement were also posted.

I'm sorry for my ignorance and my down right lack of knowledge about these subjects, but VB is very new to me so i apologize if i am making you guys pull your hair out.

**FishVal** · Jul 24 '07, 06:49 PM

Originally posted by Stang02GT

I have revisited post 26 which then refers me to another post...which then refers me to another post lol after all that has gone on today for me my head is spinning right now and trying to follow the trail of suggestions of revisiting posts i have no idea what i am supposed to be posting for you guys to look at.

i've posted my code numerous time (Neo you know all about that lol ), the error message, and the original SQL statement were also posted.

I'm sorry for my ignorance and my down right lack of knowledge about these subjects, but VB is very new to me so i apologize if i am making you guys pull your hair out.

Acid trip. lol

If you want you are welcome to send me your db via e-mail available from my vCard.

**JKing** · Jul 24 '07, 07:08 PM

Ok, let's take a look at everything provided thus far. With a little deduction you will see my conclusion at the end.

First SQL and it worked.
[code=sql]
INSERT INTO SRMisc ( [Tracker Item], Description, Comments, Requestor, [SR Num] )
SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6
FROM Tracker
WHERE (((Tracker.Z6)= "3918"));
[/code]

SQL in VB still works
[code=vb]
strSQL = "INSERT INTO SRMisc ( [Tracker Item], Description, " & _
"Comments, Requestor, [SR Num] ) " & _
"SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6 " & _
"FROM Tracker WHERE Tracker.Z6='" & _
Me.Text19 & "';"
[/code]

SQL in VB provides data type mismatch when adding or clause
[code=vb]
strSQL = "INSERT INTO SRMisc ( [Tracker Item], Description, " & _
"Comments, Requestor, [SR Num] ) " & _
"SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6 " & _
"FROM Tracker WHERE Tracker.Z6='" & Me.Z6 & "' OR Tracker.X5='" & Me.X5 & "';"
[/code]

SQL in VB removed single quotes still get error
[code=vb]
"FROM Tracker WHERE Tracker.Z6=" & Me.Z6 & " OR Tracker.X5=" & Me.X5 & ";"
[/code]

This leads me to believe that Z6 is a text and X5 is a number...
So try this:
[code=vb]
"FROM Tracker WHERE Tracker.Z6='" & Me.Z6 & "' OR Tracker.X5=" & Me.X5 & ";"
[/code]

This will hopefully work... I hope haha.

**NeoPa** · Jul 24 '07, 07:11 PM

Post #26 again then :)
Second paragraph.

Originally posted by NeoPa

...
We need to revisit posts #19 & #20 then.

Just a pointer to the request (#19) - which was for the SQL string rather than the code which creates it (which did prove some help though), and the code you posted (#20) which needed an extra line added.

Originally posted by NeoPa

To get the actual SQL string (as requested) add this line after your line #7 in post #20.

Code:

Important line missing from this point (Doh!!!)

When you run the code you should find the actual SQL string used in the immediate pane of the VBA window.
I suspect you'll find one of the values not set, but let's wait for the SQL before jumping the gun.

Having omitted the most important part of the post in #26 I'll add it here for you :embarrased:

Code:

Debug.Print strSQL

Hopefully, with the instructions included - it won't be so hard to follow.
Let me apologise again for being a numpty :(

**Stang02GT** · Jul 25 '07, 12:28 PM

SUCCESS!!!! lol

it works

JKing this line of code worked....

Code:

"FROM Tracker WHERE Tracker.Z6='" & Me.Z6 & "' OR Tracker.X5=" & Me.X5 & ";"

I can't thank all of you enough for helping me out so much!!!!

Thank you all A LOT! :)