ACCESS FEED: RE: [MS_AccessPros] Query question

Rabu, 23 Desember 2015

RE: [MS_AccessPros] Query question

Sarah,
It looks like you have "Between" inside your IIf(). This won't work. You need to remove the Between.

I suggest you build the SQL in vba and apply it as the record source of a form or replace the SQL property of a saved query.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 23 Dec 2015 12:28:26 -0800
Subject: RE: [MS_AccessPros] Query question

I finally got a chance to work on my query.

It now looks like this:

SELECT t_Vendor.*

FROM t_Vendor

WHERE (iif(NZ([Forms]![f_def_vendor_RPT]![1f7],"")>"",(Instr("," & [Forms]![f_def_vendor_RPT]![1f7] & ",", "," & [VENDID] & "'")>= 1),((NZ([VENDID],"")) Between NZ([Forms]![f_def_vendor_RPT]![1from],"") And NZ([Forms]![f_def_vendor_RPT]![1TO],"")))

ORDER BY t_Vendor.Name, t_Vendor.vendID;

The query works, except for one issue: the [VENDID] length can vary and sometimes a shorter id is contained in the longer id and will be selected even though it is not in the [1f7] field.

for example:

1f7=,ABC1,TER,XYZ1,

in this case VENDID ABC and XYZ will also be selected , because they are contained in ABC1, XYZ1 respectively.

If their any other statement besides INSTR that i can use, or is their a way to specify the length of VENDID , based on the characters between the commas?.

Sarah

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

You might be able to use an expression like:
Instr(Forms!YourForm!YourControl, [YourFieldName])>= 1

To avoid mismatches, you might need to add commas to the beginning and end of expressions:
Instr("," & Forms!YourForm!YourControl & ",", "," & [YourFieldName] & "'")>= 1

Duane Hookom MVP
MS Access

________________________________

> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Wed, 11 Nov 2015 14:46:36 -0800
> Subject: RE: [MS_AccessPros] Query question
>
>
>
>
>
> The complete field.
> Field VENDID , if it matches "ABC" or "QRS" or "XYZ" etc it should be
> selected.
> Sarah
> ---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
>
> Sarah,
> Are the values matching the complete field values ( = "ABC" )
> or containing ( Like "*ABC*" ) ?
>
> Are you checking against the same field VENDID?
>
> Duane Hookom, MVP
> MS Access
>
> ________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Wed, 11 Nov 2015 12:56:58 -0800
> Subject: [MS_AccessPros] Query question
>
>
>
> I currently include a WHERE statement in my query that checks a field
> by range.
> For Example:
> WHERE (((NZ([VENDID],"")) Between
> NZ([Forms]![f_def_vendor_RPT]![1from],"") And
> NZ([Forms]![f_def_vendor_RPT]![1TO],""))
>
>
>
> I would now like to add another selection that can check the field for
> individual values I enter in the form.
> For example "ABC", "QRS", "XYZ". If would enter these values into a
> selection box on the from (separated by commas?), and then I would like
> to include in the Query to search the Vendor ID for these values.
> Is this possible?.
>
>
> Thanks for your help.
>
>
> Sarah
>
>
>
>
>
>
>
>
>