change text to date

[redFred]

I have read Allen Bowne's method (and others reponses here) to modify a table
to change a field from text to date type.

Create new field - update old into new type - change name back to old name.

My table stores credit card expiry dates as text, MM/YY.

I want to change to date type, with the new date being MM/31/YY, or some
other day. I wish to do this to facilitate MS KB210534 credit card
expiration date method. That is, all new entries, while entered as MM/YY
would be - for expiration test purposes - considered as expiring on the last
day of the month.

My dilemma is the syntax for the update query. I'm not getting anywhere.

I appreciate any help,

 

[fredg]

I have read Allen Bowne's method (and others reponses here) to modify a table
to change a field from text to date type.

Create new field - update old into new type - change name back to old name.

My table stores credit card expiry dates as text, MM/YY.

I want to change to date type, with the new date being MM/31/YY, or some
other day. I wish to do this to facilitate MS KB210534 credit card
expiration date method. That is, all new entries, while entered as MM/YY
would be - for expiration test purposes - considered as expiring on the last
day of the month.

My dilemma is the syntax for the update query. I'm not getting anywhere.

I appreciate any help,

You can use the DateSerial function to get the last day of the expiry
month. The following should work as is up to the end of this century.

LastDay: DateSerial(20 & Right([CombinedNames],2),
Left([CombinedNames],2)+1,0)

 

[redFred]

Thanks Fred.

Did I mention I was not a professional or serious amateur?

How do I use that code in an update query? Where text field is named CCExp
and new field is named CCExpNew?

Thanks.

I have read Allen Bowne's method (and others reponses here) to modify a table
to change a field from text to date type.

Create new field - update old into new type - change name back to old name.

My table stores credit card expiry dates as text, MM/YY.

I want to change to date type, with the new date being MM/31/YY, or some
other day. I wish to do this to facilitate MS KB210534 credit card
expiration date method. That is, all new entries, while entered as MM/YY
would be - for expiration test purposes - considered as expiring on the last
day of the month.

My dilemma is the syntax for the update query. I'm not getting anywhere.

I appreciate any help,

You can use the DateSerial function to get the last day of the expiry
month. The following should work as is up to the end of this century.

LastDay: DateSerial(20 & Right([CombinedNames],2),
Left([CombinedNames],2)+1,0)

 

[John W. Vinson]

Thanks Fred.

Did I mention I was not a professional or serious amateur?

How do I use that code in an update query? Where text field is named CCExp
and new field is named CCExpNew?

PMFJI...

Create a Query based on your table. Select the CCExp and CCExpNew fields.

Change the Query to an Update query using the Query menu option or the query
type tool in the toolbar.

On the Criteria line under CCExp put

IS NOT NULL

to prevent errors from null dates.

On the UpdateTo line under CCExpNew put

DateSerial(Val("20" & Right([CCExp],2)), Val(Left([CCExp],2))+1,0)

Run the query by clicking the ! icon.

Check the results to see if they have the values you want.

John W. Vinson [MVP]

 

[John Spencer]

In the query grid
-- ADD CCEXPNew and CCExp to the grid
-- Select Query: UPdate from the menu
-- Under CCExp enter Is Not Null for the criteria
-- Under CCEXPNew enter the following in the UPDATE TO "cell"
DateSerial(Right([CCEXP],2),Left([CCExp],2)+1,0)
-- Select Query: Run from the Menu


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks Fred.

Did I mention I was not a professional or serious amateur?

How do I use that code in an update query? Where text field is named CCExp
and new field is named CCExpNew?

Thanks.

I have read Allen Bowne's method (and others reponses here) to modify a table
to change a field from text to date type.

Create new field - update old into new type - change name back to old name.

My table stores credit card expiry dates as text, MM/YY.

I want to change to date type, with the new date being MM/31/YY, or some
other day. I wish to do this to facilitate MS KB210534 credit card
expiration date method. That is, all new entries, while entered as MM/YY
would be - for expiration test purposes - considered as expiring on the last
day of the month.

My dilemma is the syntax for the update query. I'm not getting anywhere.

I appreciate any help,

You can use the DateSerial function to get the last day of the expiry
month. The following should work as is up to the end of this century.

LastDay: DateSerial(20 & Right([CombinedNames],2),
Left([CombinedNames],2)+1,0)

 

[redFred]

Thanks so much. That did the trick.

Thanks Fred.

Did I mention I was not a professional or serious amateur?

How do I use that code in an update query? Where text field is named CCExp
and new field is named CCExpNew?

PMFJI...

Create a Query based on your table. Select the CCExp and CCExpNew fields.

Change the Query to an Update query using the Query menu option or the query
type tool in the toolbar.

On the Criteria line under CCExp put

IS NOT NULL

to prevent errors from null dates.

On the UpdateTo line under CCExpNew put

DateSerial(Val("20" & Right([CCExp],2)), Val(Left([CCExp],2))+1,0)

Run the query by clicking the ! icon.

Check the results to see if they have the values you want.

John W. Vinson [MVP]

 

[redFred]

Thanks. Its working.

In the query grid
-- ADD CCEXPNew and CCExp to the grid
-- Select Query: UPdate from the menu
-- Under CCExp enter Is Not Null for the criteria
-- Under CCEXPNew enter the following in the UPDATE TO "cell"
DateSerial(Right([CCEXP],2),Left([CCExp],2)+1,0)
-- Select Query: Run from the Menu


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks Fred.

Did I mention I was not a professional or serious amateur?

How do I use that code in an update query? Where text field is named CCExp
and new field is named CCExpNew?

Thanks.

On Sat, 12 Jan 2008 07:53:00 -0800, redFred wrote:

I have read Allen Bowne's method (and others reponses here) to modify a table
to change a field from text to date type.

Create new field - update old into new type - change name back to old name.

My table stores credit card expiry dates as text, MM/YY.

I want to change to date type, with the new date being MM/31/YY, or some
other day. I wish to do this to facilitate MS KB210534 credit card
expiration date method. That is, all new entries, while entered as MM/YY
would be - for expiration test purposes - considered as expiring on the last
day of the month.

My dilemma is the syntax for the update query. I'm not getting anywhere.

I appreciate any help,
You can use the DateSerial function to get the last day of the expiry
month. The following should work as is up to the end of this century.

LastDay: DateSerial(20 & Right([CombinedNames],2),
Left([CombinedNames],2)+1,0)