Form stopped working in new year?

[daniejm]

Hey all,

My form was working perfectly last week. Alas, I came in today and it has stopped working -- with no changes having been made. Due to the nature of the form, which is explained below, I can only assume it has something to do with the new year.

The form is a birthday list compiler. In the table, every customer's birthdate has been modified to a M/D format, so Access is disregarding the year they were born. I created a form to pull the same input format (ex: 1/1) and find all the birthdays for that date, plus other pertinent customer information. Tried it this morning and all of my queries are now blank.

I'm confused by this because my form disregards the year, but the new year is the only thing that I can think of to have made an effect. The database was working last week and was untouched until today.

Ideas?

Thanks in advance.



Joe

[RuralGuy]

So the BirthDate field is now a string rather than a DateTime value, right? What does your form execute to "find all the birthdays for that date"? the

[daniejm]

Right.

The form executes a query, which uses the following expression:

Between [Forms]![BirthdayForm]![txtDate1] And [Forms]![BirthdayForm]![txtDate2]

My Form has two input boxes so I could possibly pull a date range (ex: 1/4 to 1/8), which is indicated by txtDate1 and txtDate 2.

[RuralGuy]

I would expect strange results when such a query is run against a string field.

[daniejm]

Certaintly. However, I'm trying to understand why it worked all of December (when I created it) and now the queries are blank.

If this is an instance where I need to modify the query and/or form, what is suggested to make it more stable?

[RuralGuy]

I don't have an answer for you but do you still have the actual BirthDate as a DateTime in the table? Pretty easy to create a query that only returns the Day/Month from a date field.

[daniejm]

I appreciate your prompt responses, RuralGuy.

Yes, the DataType is still Date/Time. However, I changed the format to display m/d.

[RuralGuy]

I'm sorry but I have an appointment to go to and won't be back for several hours. I'll check in when I get back.

[Rawb]

Yes, the DataType is still Date/Time. However, I changed the format to display m/d.

Even though it's only displaying the month and day, the year is still included in the data itself. And honestly, that's a good thing, even if it does make it a little more complicated.

Assuming the birthday information is stored in the Field "Birthday" in the Table "People", the query would look something like this:

SELECT * FROM People WHERE DateSerial(DatePart("yyyy",Date()),DatePart("m",[Birthday]),DatePart("d",[Birthday]))=Date()

This Query uses the builtin DatePart() Function to pull out the month and day of the date stored in [Birthday] along with the current year. Then it uses the builtin DateSerial() Function to stick those parts back together. That should give you the date that person's next birthday will fall on. From there, you can use it however you like (in the Query above, I compare it to the current date).

[daniejm]

Rawb -- Thank you. Everything looks logical. I built the expression as you mentioned, changing the appropriate variables as needed. Getting a syntax error now? This is where I'm a total novice. I've entered the expression in my Query ("BirthdayQuery") in the "Criteria" input box. Here's the expression:

SELECT * FROM [Birthday Table] WHERE DateSerial(DatePart("yyyy",Date()),DatePart("m",[B-Day]),DatePart("d",[B-Day]))=Date()

Where am I going wrong?

[Rawb]

Ah. What I gave you was the whole SQL Query, not just the Criteria. If you're using the Query Builder, you should create a new Field with the following:

Field: BdayThisYear: DateSerial(DatePart("yyyy",Date()),DatePart("m",[Birthday]),DatePart("d",[Birthday]))
Table: Blank
Sort: Blank
Show: Checked
Criteria: Date()

And yes, you'll need that colon (:) after "BdayThisYear."

Give that a try and see if it works!

[ssanfu]

Yes, the DataType is still Date/Time. However, I changed the format to display m/d.

Changing the format only changes how the data is displayed.


I used a form named "Form13", two text boxes named "startDate" and "stopDate" and a button named "cmdRequery".
The button code is:

Private Sub cmdRequery_Click()
On Error GoTo Err_cmdRequery_Click

    Me.Requery

Exit_cmdRequery_Click:
    Exit Sub

Err_cmdRequery_Click:
    MsgBox Err.Description
    Resume Exit_cmdRequery_Click
    
End Sub

Here is the query I came up with:

SELECT [Birthday Table].id, [Birthday Table].Birthday 
FROM [Birthday Table] 
WHERE (((Month([Birthday])) Between Month([forms]![form13].[startdate]) And Month([forms]![form13].[stopdate])) AND ((Day([Birthday])) Between Day([forms]![form13].[startdate]) And Day([forms]![form13].[stopdate]))) 
ORDER BY [Birthday Table].Birthday;

You need to change the form name (in blue) to your form name.....
It compares the month of the birthday to the months in the two text boxes and the day of the birthday to the days in the two text boxes.



My $0.02 worth

[daniejm]

Ah. What I gave you was the whole SQL Query, not just the Criteria. If you're using the Query Builder, you should create a new Field with the following:

Field: BdayThisYear: DateSerial(DatePart("yyyy",Date()),DatePart("m",[Birthday]),DatePart("d",[Birthday]))
Table: Blank
Sort: Blank
Show: Checked
Criteria: Date()

And yes, you'll need that colon ( after "BdayThisYear."

Give that a try and see if it works!

Hi Rawb,

This solution worked. Now, for the nit-picky stuff:

In the previous version of the form -- before it stopped working unexpectedly -- I could do this as an example:

txtDate1: 1/1
txtDate2: 1/3

When I ran the query (via the command button on the form), this input would provide me all of my clients who have birthdays within that range. This would allow me to contact them. The only way to get input from the form now is to enter:

txtDate1: 1/1/12
txtDate2: 1/3/12

For the sake of being picky, is there any way to return my form to its original state so I only have to enter MONTH/DATE to produce results?

Thank you so much.

Additionally, I appreciate your response ssanfu -- the solution was a little over my head (wasn't sure how to code a command button as suggested). Thank you.

[Rawb]

The easiest way is to add VBA Code to your Command Button (the one that you click to show the people with birthdays in your range) to convert the fields to a Date before running the Query. You should be able to do something like this:

Me!txtDate1 = CDate(Me!txtDate1)
Me!txtDate2 = CDate(Me!txtDate2)

[daniejm]

Sounds good, Rawb. I haven't tested the VBA code on the command button but you helped me get the form working again. Therefore, I'll mark this thread as solved.

Thanks again for all of your help!