Query with month problem

[Salganalyse]

I have a table with a field for the persons birthday with the content like "220361" that means March 22 in 1961. The 3. and 4. digit is for the month.

I want a query to select all persons that are born in the present month. In March the persons that are born in March are selected. In April the person that are born i April are selected.

Making a function is not allowed.
Doing it by parameter is not allowed.

I feel that I have to use some of this: now(), month(), substr(), but I am confused!

How do I do it by IIF(???)

 

[Mile-O]

An example:

SELECT DateSerial("19" & Right([DateOfBirth], 2), Mid([DateOfBirth], 3, 4), Left([DateOfBirth], 2))  AS DOB
FROM MyTable
WHERE DateSerial("19" & Right([DateOfBirth], 2), Mid([DateOfBirth], 3, 4), Left([DateOfBirth], 2)) Between [Date1 Parameter] And [Date2 Parameter];

 

[ColinEssex]

Making a function is not allowed.
Doing it by parameter is not allowed.

is this some kind of quiz?

Col

 

[Mile-O]

Is this some kind of homework?

 

[ColinEssex]

is this some kind of sneaky way to get good marks?

 

[Salganalyse]

It's not a quiz.
It's not a kind of homework or to get good marks.

I work in a bank in Norway and I want to send letters (DM) to our insuranse customers that has child and youth insuranse and are going to be 20 years old in April, May, June a.s.o. When these customers become 20 they are not covered by this insuranse anymore.
We have to offer them an adult insuranse.

In my database I have the field DateofBirth and it's made as a text field with content like "220361" and that means the customers birthday is march (03) the 22. (22) in the year 1961 (61).

I shall use this query as a mean for a segment that is used in an another program that uses Access and the query. This program do not allow userdefined functions or parameter like Between [Date of birth 1] and [Date of birth 2].
How do I select the customers born in April when we are in April, and I shall not edit the query to select the customers born in May when we are in May ?

 

[Birdy]

Try using the Instr() function.
Create a Query to run a Report. In the query, have a new field that is something like this.

iif(Instr([DOB],3,2)= [Enter Month in Digits],-1,0)
This will then select the 3 & 4 digit of the Date Of Birth where the Month is equal to the Month that the user enters IE 05 is MAY. This will then put a -1 value into the new field that you can then pick up for the Printing of the letters.

Hope this makes sense.

 

[Salganalyse]

How do I change the "[Enter Month in Digits]" (a parameter question to the user) into a computer lookup for the month the user is running this query?

Is it possible to use the Now() and Month() and translate it into text with leading zeroes ?

 

[Mile-O]

Have a look at my post above.

 

[Birdy]

Does the user enter a month at the start of the printing process. IE, how does the system know what month to print?

If the Month is selected from a dropdown, then a field can be added to the Month Table to represent the 2 digits.

 

[Salganalyse]

Birdy: No, the user does not enter a month at the start. The date the query is run is the date which also specify the month for the customers.

Now I got it:
I show only the important part of the select sentence:

WHERE (((FormatNumber(Mid([DOB],3,2),"0","0"))=Month(Now())) And .....

Don't mind the number of parantheses.

Thanks to SJ McAbney and Birdy for your help. Though I found the last answer myself, you did help me to get there.

This was my first question in this forum. I am very happy that I got answers that helped me today.
I will try to read this forum and try to help others if I can. Maybe I am not that good by now but nobody can tell for the future.

 

[Birdy]

Sal,

Glad to be of assistance.

 

[Mile-O]

Out of interest, why couldn't you use a function?