MS Access "VALUE" Function

  • Thread starter Thread starter ddunkel
  • Start date Start date
D

ddunkel

Hello,

I am a little stuck with this query. I am trying to create a query in
MS access that looks at the values in a created form an pulls like
matches out of a table. Now when all of the boxes in the form are
filled in this works great I would like to have the user leave the
field blank and have it just pull all results (Or ignore this field).
This would give the user the ability to refine their search.

I have done this same SQL in our AS400 against a different file and it
works fine but My issue is in MS access I cannot use the VALUE
function and that is the change I made in our AS400 to get it to
work.

Here is the SQL
SELECT tbl_Guides.[Item number], tbl_Guides.[Current unit cost],
tbl_Guides.MATERIAL, tbl_Guides.OVERHEAD, tbl_Guides.LABOR,
(tbl_Guides.[GR Method]=FORMS.frm_guide.GRbox), tbl_Guides.[Bore
Diameter], tbl_Guides.[Rod Diameter], tbl_Guides.[Length Of Guide],
tbl_Guides.[Lot Size], tbl_Guides.[Type Of Material], tbl_Guides.EAU,
tbl_Guides.[Tapped Holes], tbl_Guides.[Wear Ring],
tbl_Guides.Standard, tbl_Guides.[AN Wiper] INTO tbl_Master_Guides

FROM tbl_Guides

WHERE (tbl_Guides.[GR Method]=FORMS.frm_guide.GRbox) AND ((tbl_Guides.
[Bore Diameter])=(FORMS.frm_guide.borebox)) And ((tbl_Guides.[Rod
Diameter])= FORMS.frm_guide.RodBox) And ((tbl_Guides.[Length Of
Guide]) = FORMS.frm_Guide.LengthBox) And ((tbl_ .[Lot Size]) =
FORMS.frm_Guide.LotBox) And ((tbl_Guides.[Type Of Material]) =
FORMS.frm_Guide.MaterialBox) And ((tbl_Guides.EAU) =
FORMS.frm_Guide.UsageBox) And ((tbl_Guides.[Tapped Holes]) =
FORMS.frm_Guide.tappedBox)

GROUP BY tbl_Guides.[Item number], tbl_Guides.[Current unit cost],
tbl_Guides.MATERIAL, tbl_Guides.OVERHEAD, tbl_Guides.LABOR, tbl_Guides.
[GR Method], tbl_Guides.[Bore Diameter], tbl_Guides.[Rod Diameter],
tbl_Guides.[Length Of Guide], tbl_Guides.[Lot Size], tbl_Guides.[Type
Of Material], tbl_Guides.EAU, tbl_Guides.[Tapped Holes], tbl_Guides.
[Wear Ring], tbl_Guides.Standard, tbl_Guides.[AN Wiper];
 
ddunkel said:
Hello,

I am a little stuck with this query. I am trying to create a query in
MS access that looks at the values in a created form an pulls like
matches out of a table. Now when all of the boxes in the form are
filled in this works great I would like to have the user leave the
field blank and have it just pull all results (Or ignore this field).
This would give the user the ability to refine their search.

I have done this same SQL in our AS400 against a different file and it
works fine but My issue is in MS access I cannot use the VALUE
function and that is the change I made in our AS400 to get it to
work.

Here is the SQL
SELECT tbl_Guides.[Item number], tbl_Guides.[Current unit cost],
tbl_Guides.MATERIAL, tbl_Guides.OVERHEAD, tbl_Guides.LABOR,
(tbl_Guides.[GR Method]=FORMS.frm_guide.GRbox), tbl_Guides.[Bore
Diameter], tbl_Guides.[Rod Diameter], tbl_Guides.[Length Of Guide],
tbl_Guides.[Lot Size], tbl_Guides.[Type Of Material], tbl_Guides.EAU,
tbl_Guides.[Tapped Holes], tbl_Guides.[Wear Ring],
tbl_Guides.Standard, tbl_Guides.[AN Wiper] INTO tbl_Master_Guides

FROM tbl_Guides

WHERE (tbl_Guides.[GR Method]=FORMS.frm_guide.GRbox) AND ((tbl_Guides.
[Bore Diameter])=(FORMS.frm_guide.borebox)) And ((tbl_Guides.[Rod
Diameter])= FORMS.frm_guide.RodBox) And ((tbl_Guides.[Length Of
Guide]) = FORMS.frm_Guide.LengthBox) And ((tbl_ .[Lot Size]) =
FORMS.frm_Guide.LotBox) And ((tbl_Guides.[Type Of Material]) =
FORMS.frm_Guide.MaterialBox) And ((tbl_Guides.EAU) =
FORMS.frm_Guide.UsageBox) And ((tbl_Guides.[Tapped Holes]) =
FORMS.frm_Guide.tappedBox)

GROUP BY tbl_Guides.[Item number], tbl_Guides.[Current unit cost],
tbl_Guides.MATERIAL, tbl_Guides.OVERHEAD, tbl_Guides.LABOR, tbl_Guides.
[GR Method], tbl_Guides.[Bore Diameter], tbl_Guides.[Rod Diameter],
tbl_Guides.[Length Of Guide], tbl_Guides.[Lot Size], tbl_Guides.[Type
Of Material], tbl_Guides.EAU, tbl_Guides.[Tapped Holes], tbl_Guides.
[Wear Ring], tbl_Guides.Standard, tbl_Guides.[AN Wiper];
Click to expand...


I don't know what the VALUE function you mention is, but here's a way to
formulate the SQL statement for Access so that blank criterion fields on the
form are ignored:

----------------------- start of SQL ---------------------------
SELECT
[Item number], [Current unit cost],
MATERIAL, OVERHEAD, LABOR,
([GR Method]=Forms!frm_guide!GRbox),
[Bore Diameter], [Rod Diameter], [Length Of Guide],
[Lot Size], [Type Of Material], EAU,
[Tapped Holes], [Wear Ring],
Standard, [AN Wiper] INTO tbl_Master_Guides
FROM tbl_Guides
WHERE ([GR Method]=Forms!frm_guide!GRbox
OR Forms!frm_guide!GRbox Is Null)
AND ([Bore Diameter]=Forms!frm_guide!borebox
OR Forms!frm_guide!borebox Is Null)
AND ([Rod Diameter]=Forms!frm_guide!RodBox
OR Forms!frm_guide!RodBox Is Null)
AND ([Length Of Guide]=Forms!frm_guide!LengthBox
OR Forms!frm_guide!LengthBox Is Null)
AND ([Lot Size]=Forms!frm_guide!LotBox
OR Forms!frm_guide!LotBox Is Null)
AND ([Type Of Material]=Forms!frm_guide!MaterialBox
OR Forms!frm_guide!MaterialBox Is Null)
AND (EAU=Forms!frm_guide!UsageBox
OR Forms!frm_guide!UsageBox Is Null)
AND ([Tapped Holes]=Forms!frm_guide!tappedBox
OR Forms!frm_guide!tappedBox Is Null)
GROUP BY
[Item number], [Current unit cost],
MATERIAL, OVERHEAD, LABOR,
[GR Method], [Bore Diameter], [Rod Diameter],
[Length Of Guide], [Lot Size],
[Type Of Material], EAU, [Tapped Holes],
[Wear Ring], Standard, [AN Wiper];
----------------------- end of SQL ---------------------------

I don't know if that's toally correct, but it should at least show you the
idea.

I also corrected your use of "Forms." to "Forms!", which is the right way to
refer to forms. I believe the "dot" form works in recent versions of
Access, but it's not the "official" right way to do it.
 
Back
Top