Pretty new to access and trying to get a query to work.

[leprkon]

It should work with AND. Did you follow those steps exactly?

I tried multiple criteria and it still works.



Medium/Reagent Name: SDA
Lot Number: 4199901

Returns 5 records.

AH! I made a mistake on one of the criteria! It works! Thank you Thank you Thank you! You are awesome!

[leprkon]

I jumped the gun a little bit when I assumed that the problem was completely solved.

The next issue at hand involves the need to search for dates. If I need to search using dates without affecting the other queries, how would I do so? Would it require a completely different criteria? I did some reading around and there were people saying that converting the dates to VARCHAR might fix the issue. Is this true? If so, how would I go about doing this in a way that would not make things more complicated.

Thanks again, and sorry about all of the questions.

[June7]

I don't use dynamic parameterized queries. I use the method described in the Allen Browne link.

I don't think VARCHAR applies to VBA, at least I've never used it.

Possibly could create field in query with a calculation using Format() function that converts the date values to strings and apply the LIKE and wildcard criteria to those constructed fields. I've never tried.

[leprkon]

I don't use dynamic parameterized queries. I use the method described in the Allen Browne link.

I don't think VARCHAR applies to VBA, at least I've never used it.

Possibly could create field in query with a calculation using Format() function that converts the date values to strings and apply the LIKE and wildcard criteria to those constructed fields. I've never tried.

A lot of that went over my head, but I have some direction as to what to do. Thanks again for the help. I'll do what I can before coming back and asking for further assistance.

[leprkon]

So I came up with this, but for some reason I keep on getting a syntax error:
Date Prepared/ Received: [Inventory List].[Format([Date Prepared/ Received],"m/d/yyyy")]

If I remove the reference to the table, then I don't get a syntax error. However the query returns results that don't really make sense.

[June7]

DatePreparedReceived: Format([Date Prepared/ Received],"m/d/yyyy")

Name the constructed field different from the native field.

I used criteria of:
Medium/Reagent Name: SDA
Lot Number: 4199901
Date Prepared/ Received: 1/6/2015

Returns 1 record.


Advise not to use spaces or special characters/punctuation (underscore is exception) in naming convention.

[leprkon]

Ah, thank you very much.

The only real issues I am having now is that if I search for the Date Prepared/ Received by itself then I get a list of records unrelated to my search.
This is what comes up regardless of what date I put in.


This same issue comes up if I do a search in Amount Left and there are numbers populated in the Amount Left fields. I removed the Or Is Null from the end of the criteria for Amount left and I tried populating a few of the Amount Left fields with random values. However when I do a search, only the fields under Amount Left that have a 1 anywhere in their number shows up. This issue spreads to the Date Prepared/ Received search and actually breaks the other searches. I am unsure if I may have mistyped a code somewhere or not.


I uploaded my database again, just in case I may have made a big mistake somewhere. I am sorry again about dragging this on and all of the questions.

Inventory Sheet update.zip

[June7]

As I said, never seen and never tried filtering by dates this way and I don't use dynamic parameterized queries at all. I can't get it to work either. Sorry, I am stumped.

[leprkon]

As I said, never seen and never tried filtering by dates this way and I don't use dynamic parameterized queries at all. I can't get it to work either. Sorry, I am stumped.

Ah, darn. Well thanks again! I really appreciate all of the help. I'll take a look at your method and work at it one step at a time. Thanks again!

[leprkon]

Just wanted to update!

With some (a lot of) help, the query managed to work properly. I just wanted to post the code here for reference just in case anyone in the future runs into this issue!

SELECT [inventory list].[medium/reagent name], Format([date prepared/ received], "m/d/yyyy") AS DatePreparedReceived, [inventory list].[vendor, catalog or fid], [inventory list].[lot number], Format([expiration date], "m/d/yyyy") AS ExpirationDate, [inventory list].[quantity received/made], [inventory list].[amount left], Format([gpq/qc date], "m/d/yyyy") AS GPQQCDate, [inventory list].[storage condition], [inventory list].[product inspection], Format([archive date], "m/d/yyyy") AS ArchiveDateFROM [inventory list]
WHERE ( 
         (  [inventory list].[medium/reagent name] LIKE "*" & [forms]![search form]![medium/reagent name] & "*"
        OR [forms]![search form]![medium/reagent name] IS NULL
         ) 
       AND ( 
             Format([date prepared/ received], "m/d/yyyy")  LIKE 
                 "*" & [forms]![search form]![date prepared/received] & "*" 
              OR [forms]![search form]![date prepared/received] IS NULL
           ) 
       AND  ( [inventory list].[vendor, catalog or fid]  LIKE 
                 "*" & [forms]![search form]![vendor, catalog or fid] & "*" 
              OR [forms]![search form]![vendor, catalog or fid] IS NULL
            ) 
       AND ( [inventory list].[lot number]  LIKE 
                 "*" & [forms]![search form]![lot number] & "*" 
              OR [forms]![search form]![lot number] IS NULL
           ) 
       AND (  Format([expiration date], "m/d/yyyy")  LIKE 
                 "*" & [forms]![search form]![expiration date] & "*" 
              OR [forms]![search form]![expiration date] IS NULL
           ) 
       AND (  [inventory list].[quantity received/made]  LIKE 
                 "*" & [forms]![search form]![quantity made/received] & "*" 
              OR [forms]![search form]![quantity made/received]  IS NULL
           ) 
       AND (  [inventory list].[amount left]  LIKE 
                   "*" & [forms]![search form]![amount left] & "*" 
              OR  [inventory list].[amount left]  IS NULL 
            ) 
       AND (  Format([gpq/qc date], "m/d/yyyy")  LIKE 
                 "*" & [forms]![search form]![gpq/qc date] & "*" 
              OR [forms]![search form]![gpq/qc date] IS NULL
           ) 
       AND (  [inventory list].[storage condition]  LIKE 
                 "*" & [forms]![search form]![storage condition] & "*" 
              OR [forms]![search form]![storage condition] IS NULL
           ) 
       AND (  [inventory list].[product inspection]  LIKE 
                 "*" & [forms]![search form]![product inspection] & "*" 
             OR [forms]![search form]![product inspection] IS NULL
           ) 
       AND (  Format([archive date], "m/d/yyyy")  LIKE 
                 "*" & [forms]![search form]![archive date] & "*" 
             OR  [forms]![search form]![archive date] IS NULL
           ) 
       );