Query In Access 1

[SandraF]

I would like to pull out all of the records that have the month = current month

The field I am checking for that is called NExtPAymentDate and it is a date/time field.

I only care if the month is equal to the month it is when they run the query.

I have tried :

LIKE Month(Now())
Like #Month/??/?????#
Nothing is working

 

[TulsaJeff]

Sandra...you are going to just love me for this....;-)


I have figured out how to do what you want in Access.

It would probably be easier if I just E-mailed the file to you and you can see how I did it.

Let me know what you want to do.

 

[SandraF]

You can e-mail it to me if that's easier for you.

FontanaSandy@Aol.Com

THank You

 

[TulsaJeff]

Sandy...You have mail!

Please contact me if you have any questions

If anyone else needs this example just post your email address and I will send it to you.

Cheers!

 

[SandraF]

I checked my mail. was there an attachment that was supposed to be with it.

I didn't see any queries.


THANKS

 

[TulsaJeff]

Yes...there was...but I must have had a major power outage in the brain or something. I sent it again...with the attachment this timeX-)

 

[TulsaJeff]

Thought I would go ahead and post the SQL in the forum specifically for 2 different reasons:

1) Someone might be interested in the query example but not want to post their email...

2)Someone might be able to look at this and show me a simpler way to do this.

The way I did it was to build a table and named it Tbl_Payment_Information. I then created two queries with the following SQL statements:

The first query was named "Query 1"


SELECT Tbl_Payment_Information.ID, Tbl_Payment_Information.NextPaymentDueDate, IIf(Month([NextPaymentDueDate])=[CurrentMonth],"Yes","No&quot AS Month, Month(Now()) AS CurrentMonth, Year(Now()) AS CurrentYear, IIf(Year([NextPaymentDueDate])=[CurrentYear],"Yes","No&quot AS Year, Day([NextPaymentDueDate]) AS PaymentDay
FROM Tbl_Payment_Information;



The second Query was names NextPaymentDue




SELECT Query1.ID, Query1.NextPaymentDueDate
FROM Query1
WHERE (((Query1.PaymentDay)=3) AND ((Query1.Month)="Yes&quot AND ((Query1.Year)="Yes&quot);


If any Access gurus look at this by chance....no, I am not on drugs! I got to where I needed to go the only way I know how and welcome any and all suggestions on how I could have done this better.

 

[wekkew]

Hi

What a lot of SQL...

The following works if you stick it on the criteria line of a query:

Like "*/" & Format(Month(Now()),"00&quot & "/*"

Format is necessary as month will return, for example, 3 for March and it needs to be 03 for query to find anything.

kate

 

[TulsaJeff]

Well at least I can get mine to work...I am after results or rather I think Sandy was. I would be interested to know more about your above post Kate. it is very vague and is not a workable solution unless I can understand what you are trying to say.

show the actual code for getting the 3rd day of the current month and the current year by just typing in the criteria line under the date field.

I copied the above code into the criteria under the date field and it returned nothing.

Be a little more specific please

 

[wekkew]

Dear TulsaJeff

The reason I responded the way I did was because in SandraF original post she had tried to use a LIKE statement to solve the problem. Because the original post did'nt give a complete SQL string, I assumed, not unreasonably, that the query was being created in the QBE of Access and that SandraF wasn't entering a SQL string direct.

As to why you couldn't get this to work - one reason could well be that I'm on UK date format (day/month/year) and from your name I guess you might well be using US(month/day/year) - if that's the case the revised LIKE statement would be:

Like Format(Month(Now()),"00&quot & "/*/*"

and the full SQL would be:

SELECT Table1.Field1, Table1.Field2 FROM Table1
WHERE (((Table1.Field2) Like Format(Month(Now()),"00&quot & "/*/*&quot);

All you have to do to get my query to work is to create a query using the QBE and enter the line below, in the column of a DATE/TIME field, on the criteria line. Then, when you run the query the month of the current date is used to search for all matching (month) records.

HOpe that helps

Kate