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 1502 iam_clint 1,208
Recognized Expert Top Contributor
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
iam_clint 1,208
Recognized Expert Top Contributor
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
iam_clint 1,208
Recognized Expert Top Contributor
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 customers;
I can execute each statement individually but get the 'you have an error in
|
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 get
errors or the data is overwritten while they are sitting there. If I
do all select queries, 1. the records pop up if I use a "RUN" command
button, or if I have my "Report" command button set to the last query
then each time they hit one of...
|
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
Append Queries) when the Database is first opened, and then again
anytime I invoke the Macro (somehow) after the database is already
open. The latter function is useful, because the queries must be rerun
every time data is added, deleted, or...
|
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 database,just help in getting me pointed in
the right direction.
I have a database with 8 tables, which from what I have read, cannot be
linked on a single form, and be updatable. I have created a query
which includes all 8 tables, and then...
|
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 and username, password etc
will be exactly the same for each server, the only thing that will change is
the server name. Idealy I would like to get the server names from a seperate
dataset so there could be any number of servers, allthough in...
| |
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 the results of that cursor, then
run another query based on that cursor etc. My question is, how do I do this
in a VB7 aspx file? Below I have the code for the first query but I am not
sure where to go from here to run the rest of the queries that...
|
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 nominated at work to design a "system" that would allow us to track the performance of our employees. So I thought Access. Should be simple enough because I have made simple DB's with it before. I was wrong. This has turned into a bigger deal than I...
|
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 to a single query the report contains a full set of information for the plant for each of the people who work there. If I embed multiple queries into the report, the user has to input the search criteria once for each of the queries that is being...
|
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) and the 'employee
name'. There is another table which assigns an ID to the Shifts, i.e.
1,2 and 3 for morn, eve & night shifts respectively. From the mother
table, the incentive is calculated datewise for each employee as per
his shift duty. In...
|
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 samples for them to review the accounts. I was making a suggestion to my team that maybe if we only ran one Cognos query and pulled in all of the columns that everyone needs to pull samples for each of there tests that this would be better on the time...
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |