Extract characters from field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to extract the first 6 characters from a particular field and then
convert those 6 characters into a date:

Field is IDNO eg. 7107174567086

So I need 710717 to be extracted and converted to a date such as 17/07/1971
(17th July 1971)?

I have managed to get the first 6 digits but now I dont know if I can
convert to a date - is this possible?
 
Something like this, perhaps:

DateSerial(Left([IDNO], 2), Mid([IDNO], 3, 2), Mid([IDNO], 5, 2))
 
Ken

Thank you - it worked! Now I have 2 more questions:

1. If the txtIDNO field is blank then I get #Error - I have tried using the
IFF function to ignore null, this works but then the result is a text field
rather than a date field. Any ideas how to ignore the blank records?

2. Once I have the result I need then search for all records that have a
particular day and month - ignoring the year. I have set up a form with a
text box [txtBDToday] where the user will enter dd/mm eg. 17/07 and then
refered to this text box in the criteria row of the query as follows:

=[Forms]![frmReportSelection]![txtBDToday] & "*"

Access tells me that this expression is too complex!

Any ideas?

thanks

Ken Snell said:
Something like this, perhaps:

DateSerial(Left([IDNO], 2), Mid([IDNO], 3, 2), Mid([IDNO], 5, 2))

--

Ken Snell
<MS ACCESS MVP>

Cadburys said:
I need to extract the first 6 characters from a particular field and then
convert those 6 characters into a date:

Field is IDNO eg. 7107174567086

So I need 710717 to be extracted and converted to a date such as
17/07/1971
(17th July 1971)?

I have managed to get the first 6 digits but now I dont know if I can
convert to a date - is this possible?
Click to expand...
Click to expand...
 
1)
IIF(IsNull([IDNO]),Null,DateSerial(Left([IDNO], 2), Mid([IDNO], 3, 2),
Mid([IDNO], 5, 2)))
2)
As criteria for original field (IDNO):
Like "??" & Mid([Forms]![frmReportSelection]![txtBDToday],3,2) &
Left([Forms]![frmReportSelection]![txtBDToday],2) & "*"

Untested.

Regards,
Andreas

Ken

Thank you - it worked! Now I have 2 more questions:

1. If the txtIDNO field is blank then I get #Error - I have tried using the
IFF function to ignore null, this works but then the result is a text field
rather than a date field. Any ideas how to ignore the blank records?

2. Once I have the result I need then search for all records that have a
particular day and month - ignoring the year. I have set up a form with a
text box [txtBDToday] where the user will enter dd/mm eg. 17/07 and then
refered to this text box in the criteria row of the query as follows:

=[Forms]![frmReportSelection]![txtBDToday] & "*"

Access tells me that this expression is too complex!

Any ideas?

thanks

:

Something like this, perhaps:

DateSerial(Left([IDNO], 2), Mid([IDNO], 3, 2), Mid([IDNO], 5, 2))

--

Ken Snell
<MS ACCESS MVP>

I need to extract the first 6 characters from a particular field and then
convert those 6 characters into a date:

Field is IDNO eg. 7107174567086

So I need 710717 to be extracted and converted to a date such as
17/07/1971
(17th July 1971)?

I have managed to get the first 6 digits but now I dont know if I can
convert to a date - is this possible?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top