text date woes

[David R]

I inherited the management of this database from my well-meaning boss. It's a flat database with about 40 columns and 1500 entries (so far). Yargh!
However I've now run into a new snag. In a report, I can GroupOn a certain number of prefix digits, but that will only work for one given year. Minor consideration. Worse is that some are entered as 08/xx/01 and some are 8/xx/01. (Yes, they're text fields, because sometimes the value is "Deliver").
How can I summarize in a query for "Like 0n/*/01" OR "Like n/*/01"? (Since report seems limited in number manipulation)

 

[Travis]

in your query use the IIF, IsDate and Format functions.

Example:

IIF(IsDate([YourDateField])=True,Format([YourDateField],"mm/dd/yy"),[YourDateField])

What this does is Determine if the data in the field is date formatable. If it is it formats all of the dates the same. Otherwise it leaves them alone.

 

[Pat Hartman]

You will ultimately save yourself a lot of trouble if you fix up the table. Add two new columns to the table. One in date/time format to hold the date properly, the other to hold the "Deliver" value. Use the technique suggested by Travis in an update query to copy the usable dates from the text column to the date column. Then run another update query to transfer the "Deliver" code. Finally a select query to find all the rows with nulls in both new columns. These you'll need to fix manually. Once all that is done, delete the old column from the table and change everything to refer to the new columns.

If you want the user to "see" only one field on the update form, you'll need to work with an unbound control and code behind it in the Form's BeforeUpdate event to put the valid dates into the date column and the text values into the text column. You'll need slightly different code in the on current event of the form to properly display existing data.

 

[David R]

I've built a subdatasheet to put the DeviceName, DateReceived, and ToBeDelivered fields on, linked via Participant ID. All well and good.
I have a Union Query with the data source in it. DeviceName is valid and will maintain its current shape. The plan is to split Delivered into DateReceived(short date format) and ToBeDelivered(checkbox Yes/No).

However I am not familiar with Append Queries. I tried using criteria Not "Deliver" but it appends no records. "Deliver" does the same thing (i.e. nothing). How do I separate out this data? If I need to separate out "Deliver" as a text column first and then check the corresponding checkboxes by hand, that will work, but at this point I can't even separate the two. Because it is moving (eventually?) to a subdatasheet, I cannot make it an Update Query. Even if it was I do not know how to make an Update Query in SQL do what I want here.

[This message has been edited by David R (edited 10-25-2001).]

 

[David R]

Travis (or anyone)
You tried to assist me with the formatting of the dates but I don't understand how it is used. When I put it in an Update Query I get:
Function is not available in expressions in query expression 'IFF(IsDate[Date Received]=True, Format([Date Received],"mm/dd/yy"),[Date Received])'.
Everything looks correct compared to what you gave me and what I know, however. I know it will still be a text format but once I get it into the table in a consistent format I can reformat that column. Could the fact that it is a Union Query be causing it not to be able to update itself? If so, how do I get around this? I even tried writing an Append Query with no specific criteria just to copy the information into a new space.

 

[Jack Cowley]

I haven't read this entire post but your syntax for the Immediate If statement is wrong. You have 'IFF(...' and it is 'IIF(...'

 

[David R]

Thanks for catching that. I'm used to C's "if and only if" so my fingers moved faster than my brain.
I've got the data formatted and pasted into the new table. However there is a (potential) design problem that I'm not sure if there is a way around.
The primary key of the main table is the ParticipantID (mask AAAAAA). In the initial setup of the subdatasheet, everything matches because the ParticipantID was pulled from the main table. However if a ParticipantID is changed on the main table (because it is calculated from their name and address, it can be done wrong by the operator), it loses the link to the subdatasheet.
Is there a way to 'force' the linked data to change their ParticipantID field as well?

[This message has been edited by David R (edited 12-12-2001).]

 

[Pat Hartman]

If you use the relationship window to define a relationship between the two tables, you can specify "enforce referential integrity" and that will give you the option of selecting "cascade change" and "cascade delete". Cascade change will propagate any changes to the primary key of the parent table (One-side) to the foreign key (many-side) of any related tables.

HOWEVER, I strongly recommend that you use an autonumber as your primary key rather than a changeable user-defined key. Although cascade update will keep the computerized data in sync, NOTHING will keep any paper files or extracted data in sync. Also, if at some point in the future you need to move to a more robust RDBMS, you may not be able to continue using this scheme. Not all RDBMS' support cascade update.

 

[David R]

Thanks again for your help, Pat. I knew it had to exist but wasn't sure where to start looking.