|
I have a Access Form, with a series of three combo boxes.
Box 1 - City
Box 2 - State
Box 3 - ZIP
When I input the zip code, I want to populate the other two boxes based on the result of the ZIP code. I have a ZIP codee table, which has ZIP, CITY, and a second table that has State.
How should I write the query in order to accomplish this?
Thanks,
|
|
|
|
|
You can obtain the City by doing a simple SELECT on your ZIP Code table, filtering on the ZIP.
You haven't explained how your tables are related. I assume you have a City table with a foreign key to the State table. If so, write a query which joins the ZIP Code, City and State tables and fetches the corresponding State description.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hello Paul, and thank you for your response.
I need to get better explaining my brainstorming!
Ok, the Zipcode table has Zipcode, City, State Code fields. The State Table has State Code, State Abrieviation, and State Name. The two tables are related by the State Code field.
I would think that if I enter the zip, then perform an after update event that queries the Zipcode table and populates the State field of the form.
So, with the above in mind, would you think that this query would work?
SELECT [ZIP Codes].[City], [States].[State Abbreviation]
FROM [ZIP Codes], States
WHERE [Zip Codes].[ZIP Code] = '44107' and
[States].[State Code] = (SELECT [ZIP Codes].[State Code] FROM [ZIP Codes] WHERE [ZIP Codes].[ZIP Code] = '44107')
ORDER BY [ZIP Codes].[ZIP Code];
Thanks,
|
|
|
|
|
solutionsville wrote: SELECT [ZIP Codes].[City], [States].[State Abbreviation]
FROM [ZIP Codes], States
WHERE [Zip Codes].[ZIP Code] = '44107' and
[States].[State Code] = (SELECT [ZIP Codes].[State Code] FROM [ZIP Codes] WHERE [ZIP Codes].[ZIP Code] = '44107')
ORDER BY [ZIP Codes].[ZIP Code];
Your query is overly complicated and can be simplified to:
SELECT [ZIP Codes].[City], [States].[State Abbreviation]
FROM [ZIP Codes]
INNER JOIN [States]
ON [ZIP Codes].[State Code] = [States].[State Code]
WHERE [ZIP Codes].[ZIP Code] = '44107'
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Thanks Paul.
I Appreciate it.
Brian
|
|
|
|
|
Here's a tip for you. Use the Access Query design to do you table links etc, then look at query as SQL Statement and there's the required code.
Steve Jowett
-------------------------
It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
I want use ADO to get the field’s data type not the ADO’s data type but the database’s data type like ‘varchar’,’ char’.
|
|
|
|
|
You can obtain field metadata by querying the INFORMATION_SCHEMA.COLUMNS system view in the master database on your SQL Server.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
No,I mean not only the SQL Server but also other kinds of database.
|
|
|
|
|
I doubt that this is possible.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
Sql server does not support ntext in function.
Is there any way to use ntext in function?
I have a condition where a ntext column value if not exist in one table then i have to select it fom another table.
Thanks in Advance
|
|
|
|
|
I wasn't aware of this limitation with the NTEXT datatype. So maybe I'm barking up the wrong tree here, but have you tried using the COALESCE(value_which_may_be_null, value_to_return_in_case_of_first_value_being_null) function? There's also the CASE statement, if you want to check for an empty string as opposed to a NULL. I've been coding in Orcle for some months now, so apologies if i've missed the point of your question.
|
|
|
|
|
Hey
Currently I am using SQL Server, and to get the stored procedure's return value I am writing (c++):
VARIANT v;
v.vt = VT_I2;
v.intVal=0;
ptrCom->Parameters->Item[v]->Value;
What I want to know is, if this command will get the return value in Oracle as well (does the return value is 0, just like in SQL Server)?
Thanks in advance!
|
|
|
|
|
sir actually i am using asp.net with c#.
in my web application i take farpoint spreadsheet control.
On that control i want treeview.
-SectorName
Aggriculture
Company name
NagarjunFertilizer
Auto
TataMotors
Honda
IT
Infosys
Wipro
like this Treeview i want on farpoint spreadsheet.
i have 3 tables
1.tSectorMaster
SectorCode(PK)
Description
2.tAssetMaster
AssetCode(PK)
Descriptionlong
3.tAssetSectorMap
AssetSectorCode(PK)
SectorCode(FK)
AssetCode(FK)
and i want
SectorName
Description
Descriptionlong
Description
Descriptionlong
Descriptionlong
Description
Descriptionlong
Descriptionlong
|
|
|
|
|
OK - so you're using ASP.NET, but you're asking in the SQL forum how to use a third party ASP.NET control ? I'd say the people who sold you the control is who you should be asking, and if they have a SQL forum, it's the wrong place to ask, there, too..
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
|
I have some sql Update command error.
I get the following error message when i run my project.
" ERROR [42000] [Microsoft] [ODBC Microsoft Access Driver] Syntax error in UPDATE statement "
some of my code is following....
Dim CW As Integer
Dim RCW As Integer
------------------------------------------------------------------------------------------------------------------------
Dim level As String = CStr(ComboBox1.SelectedItem.ToString)
Dim exercise As String = CStr(ComboBox2.SelectedItem.ToString)
Dim FWds As String = CStr(lblFalseWords.Text)
Dim SpanTime As String = CStr(hour )
"UPDATE tblSave SET Level=' " & level & " ',Exercise=' " & exercise & " ',CWords=" & CW & ",FWords=' " & FWds & " ',WPM=" & RCW & ",SpanTime=' " & SpanTime & " ' WHERE tblSave.UserName=' " & CurrentUser & " ' "
Please tell me some suggestion.....
|
|
|
|
|
Well my first suggestion is to read up on SQL injection attacks, to understand why you should never allow any system to use this code, ever. Next, I would point out that CW and RCW are used without you giving them a value. However, I'd say your core issue is simply that you're doing your own bit of SQL injection, that values in the strings you're passing through are what's causing this SQL to break. Did you go into the debugger to look at the final SQL string and see what the final SQL line looks like ? Perhaps if you copied and pasted it into SQL Server, it would explain it even better, but the solution is to use stored procs and pass your parameters in a sane way, not by string mashing
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
In "tblSave" table, if CWords and WPM columns are of VarChar Datatype then you have to alter update query as below:
'UPDATE tblSave SET Level='" & level & "',Exercise='" & exercise & "',CWords='" & CW & "',FWords='" & FWds & "',WPM='" & RCW & "',SpanTime='" & SpanTime & "' WHERE tblSave.UserName='" & CurrentUser & "'"
And For Level, Excercise, FWords and SpanTime columns ur giving one space after single quote, if the query runs successfully that space will also insert into the database.
|
|
|
|
|
Now i get success by adding [] in Level field.
Now my success code is
"UPDATE tblSave SET [Level]='" & level & "',Exercise='" & exercise & "',CWords=" & CW & ",FWords='" & FWds & "',WPM=' & RCW & ",SpanTime='" & SpanTime & "' WHERE tblSave.UserName='" & CurrentUser & "'"
Plese tell me how do u think for above metter?
|
|
|
|
|
sounds like "Level" is a keyword and has to be put into [] if used as column.
|
|
|
|
|
how to get row values as columns.
Ismail
Software Engineer
|
|
|
|
|
I believe that's called a 'pivot table'. If you google that, you should find the help you need.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
Add the following code in your stored Procedure
DECLARE @V1 VARCHAR(1000) --Increase the size depending on your row count
DECLARE @V2 VARCHAR(500)
DECLARE @V3 VARCHAR(500)
SET @V1 = ''
DECLARE C1 CURSOR FOR
SELECT country_name FROM t_master_country
OPEN C1
FETCH C1 INTO @V3
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH C1 INTO @V2
IF @@FETCH_STATUS <> 0
PRINT ' '
ELSE
SET @V1 = @V1 + ' ' + @V2
END
SET @V1 = @V3 + ' ' + @V1
CLOSE C1
PRINT @V1 -- Return @v1
|
|
|
|
|
This i know but I want to take row as coulum and next coulum values as row values
Ismail
Software Engineer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
