Discouraged Beginner - Trying Limit Query Date to Current Month 2

[LDP]

We are dealing with documents.
I've tried all the suggestions in the archive but can't seem
to set the criteria of our date field in a query to limit the dates to the current month.
We would like to use the query to generate monthly reports of engineering document status.
I've tried Threads 701-572017,701-20360,701-487970 to no avail.
I'm thinking this must be a common, simplified criteria
expression for a query...to control/limit dates used in a report.
I've also set criteria for another field in the same query
to set the "Status" value of documents to "Approved", "Rejected", "Resubmit" etc. does that cause a problem setting criteria for more than one field?

Suggestions

 

[scriverb]

Put the following code in the criteria row of the date column that you want to select on:

Between DateSerial(Year(Date()),Month(Date()),1) And DateAdd("d",-1,DateSerial(Year(Date()),Month(DateAdd("m",1,Date())),1))

This will build two dates and then use the Between function to select the records. The first date will be the first day of the month and the second will be the last day of the month.

Let me know if this is what you were looking for.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[RickSpr]

Here's a slightly simpler solution. In a blank column in the query grid, enter the following:
Field: True (Access will change this to Expr1: True)
Show: (unchecked)
Criteria: Year([YourDate])=Year(Date()) And Month([YourDate])=Month(Date())

If you switch to SQL view, this becomes:
WHERE ((Year([YearDate])=Year(Date()) And Month([YourDate])=Month(Date())))

This matches the year and month of your date to the current year and month, instead of using BETWEEN.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

Actually, I just remembered an alternate way of specifying this same criteria. Add TWO columns to the query grid:
--First column-- --Second column--
Field: Year([YourDate]) Month([YourDate])
Show: (unchecked) (unchecked)
Criteria: Year(Date()) Month(Date())

In fact, if you switch my previously suggested query to SQL View, and then switch it back, it gets converted to this form.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[LDP]

Thanks Rick and Bob.
It works perfectly.

If the user forgets and has to run the report early in the next month do I place -1, ie.after the expression.. such as Month(Date())-1? I would have two separate queries.

Could you help with these related questions..?

We have a number of different tables listing engineering documents released, such as, Shop Drawings, Submittal Products, RFI's (Request for Information), EI's (Engineers Instructions, Drawing Transmittals etc. Can I set up a parameter query to choose which document table to run the query on? Would I have to make an Indexed Table listing of tables so the user could chose from a list box then set the record source?

Similarly, can I have the user then choose from an existing status (table)list (Approved, Rejected, Resubmit etc)to get a monthly summary of each document type filtered by status?

I'm not sure, but I believe I could have a generic report and use a table generated by the combination chosen by the two parameter query.

If I could make the monthly query applicable to a number of different tables and status's by having the user choose the paramaters it would save creating a query for each report.

Your thoughts would be very appreciated.

Thanks again everyone for your help.

 

[RickSpr]

Month(Date())-1 won't change January to December, nor will it correct the year. Use Year(DateAdd("m", -1, Date())) and Month(DateAdd("m", -1, Date())) instead.

Using a parameter to change the query's criteria is easy, but you can't use one to change the table or columns being accessed. But you can still accomplish exactly what you're after without much hard work.

First, create a form your users will use to select the report contents. It would likely have a combo box in which you list the relevant tables or document types, and another in which you list the status values. It would also have an OK or GO button to start the report.

In the form's module, create a Public variable As String. When the user clicks the OK button, you use VBA to build a SQL query string, plugging in the selected table name and status value(s), and assign it to the Public variable, then open the report.

In the report, in Design View, set the Record Source to any of the tables, so you can create controls for the fields. You should only use field names that exist in all the document tables. (If you need to use different field names depending on which table is selected, it can be done, but it's more complicated than I'm describing herein.)

In the report's module, the Open event procedure should change the RecordSource property to the value of the form's Public variable, and should then close the form (which is no longer needed). Changing the RecordSource will allow you to change the table name you're accessing as well as the criteria by which you're selecting (i.e. Status values).

You might also consider having the form compose a custom report title, and save it in a second Public variable. The report's Open event procedure can then assign that variable's value to the Caption property of a label.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[LDP]

Thanks Rick.
I will build the form and report you mentioned. It reads exactly as I would like it to function.
Unfortunatly, although the status field in the tables are joined to the same (status list) table , the field names in each table are named differently. My mistake. It's the same list of values used in all tables I could have named the field the same in all tables with no consequence...
Would it be easier to re-name the fields the same and re-build the relationships to the status list before preceding with your suggestions above?
Rick,the Public Variable 'As String'...could you pass on examples of VBA structure for a typical SQL query string? Can I find them embedded in any typical Query?
Finally, what is (was) your favourite reference for Access expression building? I find the help files limited.

Thanks Rick
Congratulations on the Top Expert award

 

[scriverb]

Getting back to your original question. To allow the users to select the Current month or the Last month you can use this expression in the criteria row of your selection field:

Between IIf([Enter 'Current' or 'Last' for the month Select: ]="Current",DateSerial(Year(Date()),Month(Date()),1),DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),1))) And IIf([Enter 'Current' or 'Last' for the month Select: ]="Current",DateAdd("d",-1,DateSerial(Year(Date()),Month(DateAdd("m",1,Date())),1)),DateAdd("d",-1,DateSerial(Year(Date()),Month(Date()),1)))

Yea, it looks a little scary and complex but it gives you the ability to run the query on two different months depending upon the current date and the month selection('Current' or 'Last') by the user. No form necessary.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[RickSpr]

Frankly, I would take the time to rename the fields simply because it's less confusing when a given piece of information always has the same name everywhere. (I don't see why you would have to rebuild relationships, though. The relationships should be updated automatically when you rename the fields.)

Skeleton code for building the SQL string in the form:

    Dim strSQL As String
    Dim strTableName As String
    Dim strFieldName1 As String, strFieldName2 As String
    Dim strStatusFieldName As String

    Select Case Me!cboDocType 
        Case "Shop Drawing" 
            strTableName = "Table1"
            strFieldName1 = "Field1"
            strFieldName2 = "Field2"
            ...
            strStatusFieldName = "Status"
        Case "Submittal Product"
            strTableName = "Table2"
            strFieldName1 = "Field3"
            strFieldName2 = "Field4"
            ...
            strStatusFieldName = "Stage"
        Case ...
    End Select

    strSQL = "SELECT " & strFieldName1 & ", " & strFieldName 2" _
             & "FROM " & strTableName" _
             & "WHERE " & strStatusFieldName & "='" & Me!cboSelectedStatus & "'"
    strPublicVar = strSQL

I learned to build expressions in Pascal 20 years ago, so I really couldn't given you any useful references. VBA expressions are built very much the same way expressions in any language are built, except that VBA tends to be heavier on string concatenation than most languages. The best advice I can give is just to study various help file examples to understand what they're doing. For string concatenation expressions such as the one above, select some sample values for the variables and write down what the contents of the resulting string would be as you work through the expression.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[LDP]

Thanks Rick:

I'll rename the fields. I'll also get a handle on the structure of the code. I have a number texts to reference and I'll look at the help file examples as you suggest.
I'm guessing the line strStatusFieldName = "Stage" was meant to read "Status"?

Thanks Rick for all your help. I put another posting in yesterday but this reply has it mostly covered.

We have a need for alot of quick filtering and searches as well. I believe I could probably build a form for parameter sets to allow searches/filters based on flexible queries as above to avoid working directly in the tables themselves.

Cheers Rick

 

[RickSpr]

No, I meant that to be "Stage", to show how you could adapt to using different field names for different tables with the same kind of information.

Be sure to check the FAQs here and in the other Access forums. Generalized search forms have been addressed many times, and I'm sure you could find a lot of ideas and sample code in them.

Thank you for the Tipmaster star.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein