Dlookup?

  • Thread starter Thread starter David
  • Start date Start date
D

David

How could you retrieve only certain records from a table that have a certain
value in a field?

I have got a table that I am trying to use as a master for other reports and
forms, the name of the table is "vehinfo" , it has 10 fields, the only 2
fields that I need to use on this form is "veh" and "type"
the "veh" is a number that identifies the vehicle
the "type" is a number that denotes if it uses diesel(uses the number "1" to
show it uses diesel) or gas(uses the number "2" to show that it uses
gasoline)

The form will have 4 textboxes linked to a table"onroad"
1 textbox will be the date(I can figured that one)
1 textbox will be the gallons used(data entry)
1 textbox will be the cost(data entry)
1 textbox will be the vehicle number

what I am trying to do is to retrieve only the records that have the value
of "1" in the "type" field along with the"veh" fields value
Is this possible and if so ,how?
thanks in advance
David
 
what I am trying to do is to retrieve only the records that have the value
of "1" in the "type" field along with the"veh" fields value
Click to expand...

Very simple! Create a Query based on your table, with a criterion of 1
on the Type field; select the Veh field. Base your Form on this Query.
 
John
How would you set the vehicles number in the form if there are multiple
vehicles in the querry

can you use the default value of the textbox on the form for this

I tried this and I got " #error " in the textbox
=[on1]![veh]
"on1" is the querry name
"veh" is the field in the querry
Thanks for responding John
David
 
John
How would you set the vehicles number in the form if there are multiple
vehicles in the querry
Click to expand...

Well, I have no idea. I don't know your table structure, the structure
of your form, or what a "vehicles number" is or means...
can you use the default value of the textbox on the form for this

I tried this and I got " #error " in the textbox
=[on1]![veh]
"on1" is the querry name
"veh" is the field in the querry
Click to expand...

And I didn't suggest using any such syntax.

Instead, try doing what I suggested:

Create a Query with criteria to select the desired records.

Use this Query as the Recordsource property for your form.

You will then have all of the fields in the Query available as Control
Sources for textboxes or other controls on the form. You don't need to
(and should not) explicitly reference the name of the query in the
control source property - if the field is named Veh, simply use Veh as
the control source.
 
John,
Maybe I havent phrased what I would like to do right
Is it possible to use the query as an index for a form(if that is the right
word)
I got the query to function properly, it does as expected.

What I would like to do is to have a form that will look up the vehicles
that use diesel only and group them together so that data entry is easier.
I have been using excel for a while and I am very used to it, Access is
something new for me and hopefully I will catch on before long, anyhow

I am trying to keep all of the diesel records seperate from the other fuels.
I am wanting to have 1 table for each fuel grade. In that table will be
vehicles that use only that grade of fuel, also in that table is a
"date",Gallons","Cost","AdvCard",AdvGallons",AdvCost"
field.
If I use only the query as a control source I cannot enter each month what
they have used, when it is used the first time the query will not be empty.
What I am looking for is for somehow to make the query a reference so that
the table will have a list of vehicles for each month, instead of defining a
seperate table for each month.
like
Veh Date Gal Cost AdvCard AdvGal AdvCost
1 3/03 10 1.00
10 3/03 5 .50
1 4/03 7 .75
10 4/03 3 .35 1003 8 .90

If I am not thinking right please correct me, There is always 2 different
ways to skin a cat
Thanks Again
David
 
John,
Maybe I havent phrased what I would like to do right
Is it possible to use the query as an index for a form(if that is the right
word)
I got the query to function properly, it does as expected.
Click to expand...

Normally a Query sits in the background. You don't open the query
datasheet, and then open a Form - instead, you use the Query as the
"Recordsource" property of the form. When you use the Form Wizard
you're asked which table *OR QUERY* you want to use as its basis. If
you choose a Query, then you can pretty much forget about the query;
just open the Form and Access will "run" the query in the background.
What I would like to do is to have a form that will look up the vehicles
that use diesel only and group them together so that data entry is easier.
Click to expand...

You can create the query as I described and base a form on it; you can
also use a Parameter Query - for instance, you could have a combo box
control on your Form's header selecting the different fuels. Some very
simple code will let you filter the Form to show just diesel, or just
gasoline, or just propane or whatever.
I have been using excel for a while and I am very used to it, Access is
something new for me and hopefully I will catch on before long, anyhow
Click to expand...

Excel is a spreadsheet. Access is a relational database. THEY ARE
DIFFERENT! You may need to expunge some of what you know about Excel
(e.g. tables don't have calculations in fields, whereas spreadsheets
do have calculations in columns).

I am trying to keep all of the diesel records seperate from the other fuels.
I am wanting to have 1 table for each fuel grade. In that table will be
vehicles that use only that grade of fuel, also in that table is a
"date",Gallons","Cost","AdvCard",AdvGallons",AdvCost"
field.
Click to expand...

That would NOT be a good design. Have one table for all your data,
with an additional field for the type of fuel. It's very easy to
separate the types of fuel using a Query or a form filter as suggested
above; it's much harder to (say) total the cost over all fuels if you
have multiple tables.
If I use only the query as a control source I cannot enter each month what
they have used, when it is used the first time the query will not be empty.
Click to expand...

Ummm... no. That is not the case. Why do you say you cannot enter what
they have used!?
What I am looking for is for somehow to make the query a reference so that
the table will have a list of vehicles for each month, instead of defining a
seperate table for each month.
like
Veh Date Gal Cost AdvCard AdvGal AdvCost
1 3/03 10 1.00
10 3/03 5 .50
1 4/03 7 .75
10 4/03 3 .35 1003 8 .90

If I am not thinking right please correct me, There is always 2 different
ways to skin a cat
Click to expand...

Add a field for the fuel type and you're heading in the right
direction.

It seems you have somehow gotten into the mindset that any subset of
the data that you want to consider as a unit should be its own table.
THIS ASSUMPTION IS INCORRECT. Queries are your friends - they let you
slice and dice the data very flexibly.
 
John,
Your Right
Thanks for putting my head back on my shoulders, I know there is a
difference inbetween the two programs and you will agree, its like getting a
new car, you always try to do things that the other car did that this one
wont do and its because of human instinct. Since you made the sun rise I
understand about your concept, and it does have a lot of creditability
inbehind it. This mornig I regrouped and started going your way, and I can
see where I was headed was in the wrong direction(we all have made wrong
turns in the past). Thanks for being blunt with me, sometimes you have to
step on someones toes to get their attention
Sincerely
David
 
Back
Top