Shanan212
asked on
Object Required Error on VBA
Dim sSQL As String
sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY],"
sSQL = sSQL & " [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
sSQL = sSQL & " SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.CITY AS [DESTIN CITY], P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![Min]+[getDestinB eyond]![MI N] AS [MIN],"
sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinB eyond]![LT L] AS LTL,"
sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinB eyond]![50 0] AS 500,"
sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBe yond]![1M] AS 1M,"
sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBe yond]![2M] AS 2M,"
sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBe yond]![5M] AS 5M,"
sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinB eyond]![10 M] AS 10M,"
sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinB eyond]![20 M] AS 20M"
sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
sSQL = sSQL & " WHERE P2CSameTx65Table.SERVICE =" & Chr(34) & [Forms]![Prov2Country]![servicecombo ] & Chr(34) & ""
sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI = " & chr(34) & userinput & Chr(34) & """
sSQL = sSQL & " ORDER BY ZONE.CITY, INTER.Class"
CurrentDb.Execute sSQL, dbFailOnError
Hi,
I am getting 'Object Required' error on this line
sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI = " & chr(34) & userinput & Chr(34) & """
my guess is some quote missing or something o.O
I tried breaking my head but couldn't find it.
Any help is appreciated!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the bold part is a combination of both texts pulled from one of the tables. Is there any other way to do this?
THanks
THanks
Shanan212,
have you read my comment ?
ASKER
Oops! I didn't
Looking at it, I didn't duplicate. It was intentional as I was combining the same text with same again to find a possible combination
That is
sSQL = sSQL & " And text1text2text1text2 =" text1text2 & text1text2" And text1text2 = " & chr(34) & userinput & Chr(34) & """
userinput = text1text2
Is this wrong to do? It worked as a query-design :o
Looking at it, I didn't duplicate. It was intentional as I was combining the same text with same again to find a possible combination
That is
sSQL = sSQL & " And text1text2text1text2 =" text1text2 & text1text2" And text1text2 = " & chr(34) & userinput & Chr(34) & """
userinput = text1text2
Is this wrong to do? It worked as a query-design :o
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ok then
change this
sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI = " & chr(34) & userinput & Chr(34) & """
with
sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"
sSQL = sSQL & " And getDestinBeyond.TCOMBI = " & chr(34) & userinput & Chr(34) & """
change this
sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI = " & chr(34) & userinput & Chr(34) & """
with
sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"
sSQL = sSQL & " And getDestinBeyond.TCOMBI = " & chr(34) & userinput & Chr(34) & """
ASKER
sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY],"
sSQL = sSQL & " [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
sSQL = sSQL & " SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.CITY AS [DESTIN CITY], P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![Min]+[getDestinB eyond]![MI N] AS [MIN],"
sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinB eyond]![LT L] AS LTL,"
sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinB eyond]![50 0] AS 500,"
sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBe yond]![1M] AS 1M,"
sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBe yond]![2M] AS 2M,"
sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBe yond]![5M] AS 5M,"
sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinB eyond]![10 M] AS 10M,"
sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinB eyond]![20 M] AS 20M"
sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
sSQL = sSQL & " WHERE P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"
sSQL = sSQL & " And getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & ""
sSQL = sSQL & " ORDER BY getDestinBeyond.[DESTIN CITY], P2CSameTx65Table.Class"
CurrentDb.Execute sSQL, dbFailOnError
This is what I have latest. The object error is gone but now I am getting too few parameters; expected 2 error.
I ran simlary query by building a quick one (just now) via query-design and it worked perfectly!
Thanks for continues help (*me very tired*)
try this, make sure your table names and fields are not mispelled
sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY],"
sSQL = sSQL & " [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
sSQL = sSQL & " SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.CITY AS [DESTIN CITY], P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![Min]+[ getDestinB eyond]![MI N] AS [MIN],"
sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[ getDestinB eyond]![LT L] AS LTL,"
sSQL = sSQL & " [P2CSameTx65Table]![500]+[ getDestinB eyond]![50 0] AS [500],"
sSQL = sSQL & " [P2CSameTx65Table]![1M]+[g etDestinBe yond]![1M] AS [1M],"
sSQL = sSQL & " [P2CSameTx65Table]![2M]+[g etDestinBe yond]![2M] AS [2M],"
sSQL = sSQL & " [P2CSameTx65Table]![5M]+[g etDestinBe yond]![5M] AS [5M],"
sSQL = sSQL & " [P2CSameTx65Table]![10M]+[ getDestinB eyond]![10 M] AS [10M],"
sSQL = sSQL & " [P2CSameTx65Table]![20M]+[ getDestinB eyond]![20 M] AS [20M]"
sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
sSQL = sSQL & " WHERE P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"
sSQL = sSQL & " And getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & ""
sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY],"
sSQL = sSQL & " [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
sSQL = sSQL & " SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.CITY AS [DESTIN CITY], P2CSameTx65Table.PROVINCE AS [DESTIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![Min]+[
sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[
sSQL = sSQL & " [P2CSameTx65Table]![500]+[
sSQL = sSQL & " [P2CSameTx65Table]![1M]+[g
sSQL = sSQL & " [P2CSameTx65Table]![2M]+[g
sSQL = sSQL & " [P2CSameTx65Table]![5M]+[g
sSQL = sSQL & " [P2CSameTx65Table]![10M]+[
sSQL = sSQL & " [P2CSameTx65Table]![20M]+[
sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
sSQL = sSQL & " WHERE P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"
sSQL = sSQL & " And getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & ""
ASKER
Nope same thing!
So this is the query I created via query-design and outputting required results like a charm!
You can see that only thing changed is that
(getDestinBeyond.TCOMBI)=" HALIFAXNOV A SCOTIA"
instead of which I am using variable called 'userinput'
So this is the query I created via query-design and outputting required results like a charm!
SELECT P2CSameTx65Table.[ORIGIN CITY], P2CSameTx65Table.[ORIGIN PROVINCE], P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE], P2CSameTx65Table.TCOMBO, getDestinBeyond.TCOMBI, P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, P2CSameTx65Table.MIN, P2CSameTx65Table.LTL, P2CSameTx65Table.[500], P2CSameTx65Table.[1M], P2CSameTx65Table.[2M], P2CSameTx65Table.[5M], P2CSameTx65Table.[10M], P2CSameTx65Table.[20M]
FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]
WHERE (((P2CSameTx65Table.TCOMBO)=[getDest inBeyond]! [TCOMBI] & [getDestinBeyond]![TCOMBI] ) AND ((getDestinBeyond.TCOMBI)= "HALIFAXNO VA SCOTIA"))
ORDER BY P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.Class;
You can see that only thing changed is that
(getDestinBeyond.TCOMBI)="
instead of which I am using variable called 'userinput'
what are the values in field
P2CSameTx65Table.TCOMBO ?
and what is the value of "userinput", where you are getting errors
P2CSameTx65Table.TCOMBO ?
and what is the value of "userinput", where you are getting errors
ASKER
If it were to run, the result table would have
TCOMBO
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
etc
TCOMBI (from getDestinBeyond)
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
So what my logic behind this query is, get a combination out of main table (which contains TCOMBO) where the origin and destination combination are same
aka
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
TCOMBO
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
etc
TCOMBI (from getDestinBeyond)
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
HALIFAXNOVA SCOTIA
So what my logic behind this query is, get a combination out of main table (which contains TCOMBO) where the origin and destination combination are same
aka
HALIFAXNOVA SCOTIAHALIFAXNOVA SCOTIA
ASKER
Error on this line:
CurrentDb.Execute sSQL, dbFailOnError
regardless of values, the error shouldn't be there eh! I even eliminated the line
'sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"
and the error is still there
See my current screen (double screen) On left the query I created to run the same query (I posted the sql behind it above) and on the right is the function.
Maybe I have to redefine the "currentdb" part?
1.PNG
CurrentDb.Execute sSQL, dbFailOnError
regardless of values, the error shouldn't be there eh! I even eliminated the line
'sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"
and the error is still there
See my current screen (double screen) On left the query I created to run the same query (I posted the sql behind it above) and on the right is the function.
Maybe I have to redefine the "currentdb" part?
1.PNG
upload your db..
ASKER
ok ok :) I figured it out. I will upload it after this one (if we can't solve it)
The problem was that this line, my original coding was wrong and this is correct
sSQL = sSQL & " P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE],"
but now I am getting this error; syntax one so it should be easy to solve
The problem was that this line, my original coding was wrong and this is correct
sSQL = sSQL & " P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE],"
but now I am getting this error; syntax one so it should be easy to solve
ASKER
Progress but still syntax error in the bottom block
I changed this: so current code is
I changed this: so current code is
sSQL = "INSERT INTO [PROVINCE TO COUNTRY RATES] ([ORIGIN CITY], [ORIGIN PROVINCE], [DESTIN CITY],"
sSQL = sSQL & " [DESTIN PROVINCE], SERVICE, Class, [MIN], LTL, 500, 1M, 2M, 5M, 10M, 20M)"
sSQL = sSQL & " SELECT '" & ocity & "' as [ORIGIN CITY], '" & oprov & "' as [ORIGIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.[DESTIN PROVINCE],"
sSQL = sSQL & " P2CSameTx65Table.SERVICE, P2CSameTx65Table.Class, [P2CSameTx65Table]![MIN]+[getDestinB eyond]![MI N] AS [MIN],"
sSQL = sSQL & " [P2CSameTx65Table]![LTL]+[getDestinB eyond]![LT L] AS LTL,"
sSQL = sSQL & " [P2CSameTx65Table]![500]+[getDestinB eyond]![50 0] AS [500],"
sSQL = sSQL & " [P2CSameTx65Table]![1M]+[getDestinBe yond]![1M] AS [1M],"
sSQL = sSQL & " [P2CSameTx65Table]![2M]+[getDestinBe yond]![2M] AS [2M],"
sSQL = sSQL & " [P2CSameTx65Table]![5M]+[getDestinBe yond]![5M] AS [5M],"
sSQL = sSQL & " [P2CSameTx65Table]![10M]+[getDestinB eyond]![10 M] AS [10M],"
sSQL = sSQL & " [P2CSameTx65Table]![20M]+[getDestinB eyond]![20 M] AS [20M]"
sSQL = sSQL & " FROM getDestinBeyond INNER JOIN P2CSameTx65Table ON getDestinBeyond.[DESTIN CITY] = P2CSameTx65Table.[DESTIN CITY]"
sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]"""
sSQL = sSQL & " AND getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & ""
sSQL = sSQL & " ORDER BY P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.Class;"
CurrentDb.Execute sSQL, dbFailOnError
ASKER
Solved it :)
Thank you for breaking your head with me :)
sSQL = sSQL & " WHERE [P2CSameTx65Table].TCOMBO = [getDestinBeyond].[TCOMBI]& getDestinBeyond.[TCOMBI] "
sSQL = sSQL & " AND getDestinBeyond.TCOMBI = " & Chr(34) & userinput & Chr(34) & ""
sSQL = sSQL & " ORDER BY P2CSameTx65Table.[DESTIN CITY], P2CSameTx65Table.Class;"
Thank you for breaking your head with me :)
ASKER
Thanks all!
you should have copied and paste my post at http:#a36576656
ASKER
I did but at that time it gave a diff error since there was no
P2CSameTx65Table.CITY
There was already the column in the table called this
P2CSameTx65Table.[DESTIN CITY]
so I aborted your code :o
P2CSameTx65Table.CITY
There was already the column in the table called this
P2CSameTx65Table.[DESTIN CITY]
so I aborted your code :o
so you did not read this part
"try this, make sure your table names and fields are not mispelled "
at http:#a36576656
"try this, make sure your table names and fields are not mispelled "
at http:#a36576656
ASKER
I did :o
Its not mispelled. What happened is, I am running this query for 2 senarios. 1st senario, the table's column name is spelled as "CITY" and 2nd senario (which I am working) its spelled as "DESTIN CITY"
But I didn't actually go into table and look at it as I copied the query sql from previous senario and thought everything is ok. When you posted that comment, I actually checked for 'spelling mistakes' rather than the whole field being different; which I checked later.
Its not mispelled. What happened is, I am running this query for 2 senarios. 1st senario, the table's column name is spelled as "CITY" and 2nd senario (which I am working) its spelled as "DESTIN CITY"
But I didn't actually go into table and look at it as I copied the query sql from previous senario and thought everything is ok. When you posted that comment, I actually checked for 'spelling mistakes' rather than the whole field being different; which I checked later.
oh, sorry i should have posted
"try this, make sure your table names and fields are correct or are not mispelled "
"try this, make sure your table names and fields are correct or are not mispelled "
ASKER
Its all good. I got the program working now as I wished. The project is done for good (for now)
Again, what I did is this
Generate 17 cases x 10 columns for each city
3 senarios (3 diff forms)
each form does
1. Point(city) to Point
2. Point to Country
3. Province to Country
So the last one...say a province has 880 cities and country has 3370 cities (this is the approximate data I am working with)
This would result in a table output
880 x 3370 x 17 = nearly 5M rows x 20 colms
^ this being the max. I haven't actually run this yet as testing just begun. I doubt I will be/user will eb able to save it as access. So I am writing a manual to tell the user to use the data, then clear the output table (via button) before exiting as maximum file size in access is 2GB and access is 1M lines.
Thanks for all the help! I learned alot!
Again, what I did is this
Generate 17 cases x 10 columns for each city
3 senarios (3 diff forms)
each form does
1. Point(city) to Point
2. Point to Country
3. Province to Country
So the last one...say a province has 880 cities and country has 3370 cities (this is the approximate data I am working with)
This would result in a table output
880 x 3370 x 17 = nearly 5M rows x 20 colms
^ this being the max. I haven't actually run this yet as testing just begun. I doubt I will be/user will eb able to save it as access. So I am writing a manual to tell the user to use the data, then clear the output table (via button) before exiting as maximum file size in access is 2GB and access is 1M lines.
Thanks for all the help! I learned alot!
sSQL = sSQL & " And P2CSameTx65Table.TCOMBO = getDestinBeyond.[TCOMBI] & getDestinBeyond.[TCOMBI]" And getDestinBeyond.TCOMBI = " & chr(34) & userinput & Chr(34) & """