Refering to concrete value ina table

[finberg]

I do have a table [Commodities] with fields (Code(key);Number;Commodity name;A;B;C;D;E;F;Special conditions)
A;B;C;D;E;F stands for different directions (Europe, USA,ETC)
The data in the table are the rates for different commodities depending on a zone.
Zones are described in a separate table [Directions] with fields (Code;Description)
I do have a form in which user select a commodity from the list (first 2 fields from the table commodity) amd a zone (from a [Directions] table).
I want in a separate field to get the rate for that commotity and concrete zone.
Please give a hint how it could be done.

 

[KenReay]

Hi

I am not sure that I understand your table structure for the Commodity Table. Are you saying that you have 'n' columns, named A, B, C ..etc and that the content of the column is the rate for the corresponding zone ?

If yes, then if your Commodity table is open as recordset Rs, and is positioned on the relevant commodity, and your Directions table is open as redcord set RsD and sitting on the relevant Direction then Rs(RsD!Code)) would do it I would say.

But maybe I have not understood your table structure correctly?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[finberg]

Thanks for your answer.You are right.
Could you please comment a bit your answer
i a form the user imputs
1.Code of commodity (selecting from the list)-stored in A
2.Letter of zone -stored in B
So for that field i shall use such sequence of actions:

2. SELECT Commodities.Code, Commodities.[Their number], Commodities.Commodity, Commodities.A, Commodities.B, Commodities.C, Commodities.D, Commodities.E, Commodities.F, Commodities.Conditions, FROM Commodities
WHERE (((Commodities.Code)=));
Then I get the recorsheet with 5 values
How can I get only one I need (from the relevant column- A,B,C,D,E or F
Thanks a lot

 

[KenReay]

Hi

Are you building the SQL statement in code? like so

strSQL = "SELECT Commodities.Code, Commodities.[Their number], Commodities.Commodity, Commodities." & Me.B & "Commodities.Conditions, FROM Commodities
WHERE (((Commodities.Code)=));"



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[finberg]

With my code I got the recordset with 7 values.How can I get the value that from the column with the name zone
(chosen from the Directions table)

Me.commodity - commodity code
Me.zone- zone code



Private Sub Command38_Click()
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim Rsd As DAO.Recordset
Set db = CurrentDb
Set Rs = db.OpenRecordset("SELECT Commodities.*, Commodities.Code FROM Commodities WHERE ((Commodities.Code)=" & Me.Commodity & &quot))&quot

 

[KenReay]

Hi

You are confusing me you keep changing the SQL !

If the Selected Zone is in Me.Zone, then

Set Rs = db.OpenRecordset("SELECT Commodities.*, Commodities.Code FROM Commodities WHERE ((Commodities.Code)=" & Me.Commodity & &quot))&quot

Rs(Me.Zone) is the value you want I think

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[finberg]

Thanks a lot for your help.
I understoof my mistake and rebuild the table with rates to standart (code,commodity,zone,rate)
Now I run the query
SELECT Rate.Rate
FROM Rate
WHERE (((Rate.Code)=1) AND ((Rate.Zone)="A&quot);
and get the value I need.
How can I transfer this value to a variable?
Thanks

 

[KenReay]

Hi

if you create a recordset (set rs = db.openrecordset..etc)

then Rs!Rate is the value you want and can be assigned to a variable in the usual way:

myvar = rs!rate

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[finberg]

Please advice where is a syntax error- pardon I am really a big dummer in VBA:
Private Sub Command38_Click()
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select Rate.* from Rate WHERE (((Rate.Code)=" & Me.Commodity & " ) AND ((Rate.Zone)= ''" & Me.Zone & "''))&quot
MsgBox (Rs!Rate)
End Sub

 

[KenReay]

Hi

on which line ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[finberg]

in this one:
Set Rs = db.OpenRecordset("Select Rate.* from Rate WHERE (((Rate.Code)=" & Me.Commodity & " ) AND ((Rate.Zone)= ''" & Me.Zone & "''))&quot

 

[KenReay]

Hi

Try

Set Rs = db.OpenRecordset("Select Rate.* from Rate WHERE Rate.Code = " & Me.Commodity & " AND Rate.Zone = '" & Me.Zone & "'&quot

or if Rate.Code is a string field

Set Rs = db.OpenRecordset("Select Rate.* from Rate WHERE Rate.Code = '" & Me.Commodity & "' AND Rate.Zone = '" & Me.Zone & "'&quot



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[finberg]

No way- getting an error no current record:
Zone is a letter
Dim db As DAO.Database
Dim Rs As DAO.Recordset
Dim a As Byte
Set db = CurrentDb
Set Rs = db.OpenRecordset("Select Rate.* from Rate WHERE Rate.Code = " & Me.Commodity & " AND Rate.Zone = '" & Me.Zone & "'&quot
a = Rs!Value
MsgBox (a)

 

[KenReay]

Hi

You should really check that records have been returned so

If Rs.RecordCount > 0 Then
a= rs!Value
msbBox(a)
End If

but as for which column you should be etsting for what, I cannot say, it is your table structure you know it betetr than I, I was jsut trying to give you the idea of what you needed to do

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK