I think this would be the proper title for the problem im currently having.
Just a jyst of what the problem im running into is....
1) This page was coded by yet again the same employee whom used to work here a few years back, with time, we need to update.
2) The problem is, we used to only ship via UPS, but now that we offer shipping through other means. However, the page I am working on only allows for orders with UPS tracking numbers to show.
Example
Order Number Status Tracking Number
9999999 Shipped 1ZXXXXXXXXXX
9999998 Shipped
From the top we see that it will show the UPS but not the USPS order. I changed the code up and was able to switch it around vice versa and was dumbfounded as to why it wouldnt show both.
Now that ive confirmed that both items are in two different tables of the db I can figure out how to code it so that I can access both tables, while retaining information from both. (Am I even making any sense?)
Heres a portion of the code. -
i = 0
-
-
if not rstemp.eof then
-
-
set rs = createobject("adodb.recordset")
-
-
do until rstemp.eof OR howmanyrecs>=maxrecs
-
-
invoice_id = rstemp("unique_key")
-
invoice_status = rstemp("invoice_status")
-
-
-
invoice_tracking = ""
-
-
-
set rs = createobject("adodb.recordset")
-
rs.open "select * from invoices where invoice_tracking = '" & rstemp("invoice_tracking") & "'",strDSN,3,1
-
-
-
if not rs.eof then
-
if isnull(rs("invoice_tracking")) or rs("invoice_tracking") = null then
-
invoice_tracking = "NO TRACKING # YET"
-
else
-
invoice_tracking = rs("invoice_tracking")
-
end if
-
end if
-
-
-
x_1 = x_1 + 1
-
-
row_name = "abc" & trim(rstemp("invoice_tracking")) & "_" & x_1
-
row_name = replace(row_name," ","")
-
row_name = replace(row_name,".","")
-
row_name = replace(row_name,"'","")
-
row_name = replace(row_name,"@","")
-
row_name = replace(row_name,"?","")
-
row_name = replace(row_name,"%","")
-
row_name = replace(row_name,"!","")
-
row_name = replace(row_name,"#","")
-
row_name = replace(row_name,"$","")
-
row_name = replace(row_name,"^","")
-
row_name = replace(row_name,"*","")
-
row_name = replace(row_name,"(","")
-
row_name = replace(row_name,")","")
-
row_name = row_name & "_" & i
-
-
i = i + 1
-
if i mod 2 = 0 then
-
response.write "<tr bgcolor='#cccccc'>"
-
else
-
response.write "<tr bgcolor='#ffffff'>"
-
end if
-
-
if instr(invoice_tracking,"1Z5354RV") then
-
invoice_tracking = "<a href=pages_x_orders_tracking.asp?rid=" & invoice_tracking & ">" & invoice_tracking & "</a>"
-
end if
-
-
if instr(invoice_tracking,"1Z2Y3656") then
-
invoice_tracking = "<a href=pages_x_orders_tracking.asp?rid=" & invoice_tracking & ">" & invoice_tracking & "</a>"
-
end if
-
-
if instr(invoice_tracking,"9101") then
-
invoice_tracking = "<a href=pages_x_orders_tracking.asp?rid=" & invoice_tracking & ">" & invoice_tracking & "</a>"
-
end if
-
-
response.write "<font size='2'>"
-
response.write "<td valign='top'><font face='Arial' size='2'>" & invoice_id & "</font></td>"
-
response.write "<td valign='top'><font face='Arial' size='2'>" & invoice_status & "</font></td>"
-
response.write "<td valign='top'><font face='Arial' size='2'>" & invoice_tracking & "</font></td>"
-
response.write "</font></tr>"
-
-
Thanks in advance everyone,
Gregory Gawaran
8 1491
I wouldn't have used two different tables
but you can join the tables togethor in a query
select * from tracking t
left outer join other_tracking o on t.column = o.column
where yadda = 1
order by date
to join them togethor where i put t.column = o.column there has to be something to match one table to the other like user_Id or something so it would be like on t.user_id = o.user_id
so instead of this -
set rs = createobject("adodb.recordset")
-
rs.open "select * from invoices where invoice_tracking = '" & rstemp("invoice_tracking") & "'",strDSN,3,1
-
use this? -
select * from tracking t(t being the element in the table tracking?)
-
left outer join other_tracking(which would be the other table?) o on t.column = o.column
-
where yadda = 1 (im abit confused by this piece of code)
-
order by date
-
Thanks,
Gregory Gawaran
this is how i interpretted what you said to do, but, am not sure if its right, well everything but the yadda part haha. -
set rs = createobject("adodb.recordset")
-
rs.open "select * from tracking tracking_number
-
left outer join invoices invoice_tracking on tracking_number.column = invoice_tracking.column
-
where yadda = 1
-
order by date"
-
& rstemp("invoice_tracking") & "'",strDSN,3,1
-
thanks,
Gregory Gawaran
column actually needs to be a column name, one where it = the other
ah alrighty, as for yadda, what goes there?
I was having a problem with unterminated string, but i have fixed that problem (i think?) and now the error im getting is "" Microsoft OLE DB Provider for ODBC Drivers error '80040e21' ODBC driver does not support the requested properties."" its saying the error is on 184, which is where this string of code sits.
This is the code I have for the combine, but I don't know if its correct and is causing the problem but here is the code. -
set rs = createobject("adodb.recordset")
-
rs.open "select * from tracking tracking_number left outer join invoices invoice_tracking on tracking.tracking_number = invoices.invoice_tracking where yadda = 1 order by date" & rstemp("invoice_tracking") & "'",strDSN,3,1
-
-
Im assuming that for the column =, the first part is the table, and the second after the . is the column within the table?
Thanks again in advance,
Gregory Gawaran
yes -
rs.open "select * from tracking tracking_number left outer join invoices invoice_tracking on tracking.tracking_number = invoices.invoice_tracking order by date",strDSN,3,1
-
yes -
rs.open "select * from tracking tracking_number left outer join invoices invoice_tracking on tracking.tracking_number = invoices.invoice_tracking order by date",strDSN,3,1
-
thanks alot clint, learned something new yet again thanks to you guys.
Gregory Gawaran
PS
Keep up the great help you guys offer us newbies.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: DG |
last post by:
Hi,
Can anyone advise how to execute multiple statements in a single query
batch. For example-
update customers set customer_name = 'Smith' where customer_name =
'Smyth';
select * from...
|
by: Jeremy |
last post by:
I have built a form that calls queries. I have the first 2 set up as
select queries, and the third set up as a make table query. When
multiple users are on this application at the same time, they...
|
by: MHenry |
last post by:
Hi,
I know virtually nothing about creating Macros in Access.
I would appreciate some help in creating a Macro or Macros that
automatically run(s) 14 Queries (three Make Table Queries, and 11...
|
by: dskillingstad |
last post by:
I've been struggling with this problem for some time and have tried
multiple solutions with no luck.
Let me start with, I'm a novice at Access and I'm not looking for
someones help to design my...
|
by: Dave Edwards |
last post by:
I understand that I can fill a datagrid with multiple queries, but I cannot
figure out how to fill a dataset with the same query but run against
multiple SQL servers, the query , table structure...
|
by: mattcatmattcat |
last post by:
I have a VB7 aspx file I am creating that requires multiple queries each
dependant on the previous queries results. If I run these queries in foxpro,
I just run a query then create a cursor with...
|
by: beretta819 |
last post by:
Ok, so I apologize in advance for the wordiness of what follows... (I am not looking for someone to make this for me, but to point me in the right direction for the steps I need to take.)
I was...
|
by: vaiism |
last post by:
I am creating a report that outputs the contact information and details about a water treatment plant, and needs to include information about people who work there.
If I tie all the information...
|
by: shriil |
last post by:
Hi
I have this database that calculates and stores the incentive amount
earned by employees of a particular department. Each record is
entered
by entering the Date, Shift (morn, eve, or night)...
|
by: Supermansteel |
last post by:
My team at work uses Cognos to run multiple queries to pull in data. Then they take that data and import into Access and then usually run an Append Query to run a RND function to pull out a few...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |