A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Date query



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 08:55 PM posted to microsoft.public.access.forms
SeeAll
external usenet poster
 
Posts: 25
Default Date query

Hi,

I am using a census database to enter data from the 1901 UK census. To calculate the year born the input age is subtracted from 1901. However, when a child under one year old is enter, obviously an error shows.

What do I need to do to show 1901 when a child's age under one year is entered. The age field can hold three numbers and one letter (h = hours, d=days, w=weeks, m=months) if needed, eg., 5, 100, 5w.

Regards

Jeff
Ads
  #2  
Old June 2nd, 2010, 09:49 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Date query

Jeff

You mentioned subtracting the input age from 1901, but then you mention the age field holding only hours, days, weeks or months.

How are you doing that subtraction if the person is over 1 year old?

And it sounds like you are trying to calculate the year born ... if the person is under one year old, weren't they born in 1900? Could you just check the "age field" to see if there's a letter, then decide they were less than one?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"SeeAll" wrote in message ...
Hi,

I am using a census database to enter data from the 1901 UK census. To calculate the year born the input age is subtracted from 1901. However, when a child under one year old is enter, obviously an error shows.

What do I need to do to show 1901 when a child's age under one year is entered. The age field can hold three numbers and one letter (h = hours, d=days, w=weeks, m=months) if needed, eg., 5, 100, 5w.

Regards

Jeff
  #3  
Old June 2nd, 2010, 10:51 PM posted to microsoft.public.access.forms
SeeAll
external usenet poster
 
Posts: 25
Default Date query

Hi Jeff,

the age field can hold 4 characters, 3 numerals (1-100) and either h,d,w, or m., dependent on the input age. I input the age into the age field, e.g., age 45 this is subtracted from 1901 = 1856 (which is entered into the year born field). If a child is, let's say 8 months, then I enter [space][space]8m. When 8m is subtracted from 1901, I would like it show 1901 in the year born field.

Regards

Jeff
  #4  
Old June 2nd, 2010, 11:09 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Date query

So if you use a query to inspect that field, and it sees any characters, that makes it a less-than-one year old, right?

I guess it would depend on whether the 1901 is 1/1/1901 or 12/31/1901...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"SeeAll" wrote in message ...
Hi Jeff,

the age field can hold 4 characters, 3 numerals (1-100) and either h,d,w, or m., dependent on the input age. I input the age into the age field, e.g., age 45 this is subtracted from 1901 = 1856 (which is entered into the year born field). If a child is, let's say 8 months, then I enter [space][space]8m. When 8m is subtracted from 1901, I would like it show 1901 in the year born field.

Regards

Jeff
  #5  
Old June 4th, 2010, 09:41 AM posted to microsoft.public.access.forms
SeeAll
external usenet poster
 
Posts: 25
Default Date query

Hi Jeff,

thanks for your input. Query written and working.

Regards

Jeff
  #6  
Old June 4th, 2010, 04:35 PM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Date query

For the benefit of potential future posters looking for a similar solution, consider posting the SQL statement of your query...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"SeeAll" wrote in message ...
Hi Jeff,

thanks for your input. Query written and working.

Regards

Jeff
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 OfficeFrustration.
The comments are property of their posters.