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
>
>
>
>
>
>
>
>
>
 

 


__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (27)

.

__,_._,___

Tidak ada komentar:

Posting Komentar