Full Version: date and text field in one table
UtterAccess Forums > Microsoft® Access > Access Forms
TreeBob
I am trying to make a lookup table for my database. In the lookup table will be No, NA, and a date.
How do I have both a date and a text field in the same table column. Is there some sort of programing involved? Any ideas would be great.
James
GroverParkGeorge
A little more detail will help someone figure out how to respond. Thanks.
eorge
gersheff
I want to create a combo box in a form in which I can select "No" if there is no date to enter or an actual date if there is one to input
TreeBob
George, gersheff is my co-worker. He will give you the details.
Larry Larsen
Hi James (co-worker)
You could have a "Value List" combo that has No;N/A;Date then in the afterupdate event of the combo set your date to a form control or any other value:
eg:
If Me.ComboName ="Date" Then
Me.ControlName = Date()
End IF
TH's
thumbup.gif
GroverParkGeorge
You can try a UNION query as the row source for the combo box.
It would look something like this.
Select tblMyTable.TransactionDate as SelectDate
FROM tblMyTable
UNION Select "None" as SelectDate;
In the "AfterUpdate" event of the combo box, you'll need to use a conditional statement to evaluate the two types of responses differently.
HTH
George
gersheff
Sorry guys, I am totally clueless in what you mean. Please give more details. Also, I don't want todays date. I want the user to be able to input any date they want in the form "10/Nov/02"

George,

Ounderstand the first two lines

Select tblDate.Date as SelectDate
FROM tblDate

Larry,

What is "Value List"?

Edited by: gersheff on Wed Nov 10 15:54:25 EST 2004.
GroverParkGeorge
Let's take a step back.
That date, or dates, do you want to display in the combo box? I started out assuming (perhaps wrongly) that you want to select one date from a list of dates in the combo box because you referred to a look up table.
George
gersheff
Sorry, let me explain.
The user will have an option to either select the date is "N/A', "No", or will have the opportunity to enter a date.
GroverParkGeorge
And what will be done with this date value?
Is it stored to a field in a table? Or will you use it to lookup a value in a table?
George
gersheff
it will be stored in a table
GroverParkGeorge
So your real problem is that you want to store either a date or a text value in that field in the table?
You can't do that.

What you CAN do is simply leave the date field empty (i.e., it is NULL) if there is no date value to store.

If you want to display the text string "None" in a control on a form, you can do that with a conditional:

Iif(IsNull("TransactionDate"),"None",TransactionDate)

in the control source for that field.

George
gersheff
yes that is exactly it. ok thank you anyways. how would i make it NULL?
GroverParkGeorge
It is null until you enter something. In other words, if you don't enter a date, it will be null.
eorge
gersheff
ok that's what i thought. I was just making sure.
where would i put the "If" statement in a text box?
GroverParkGeorge
You can place the following in the control source for the field.
IIf(IsNull([TransactionDate]),"None",[TransactionDate])
where "TransactionDate" is the name of the field in your table.
Make sure the name of the control on the form is different, e.g. "txtTransactionDate" from the field.
This field with a calculated value cannot be updated.
George
SerranoG
Hi, Gersheff. I've been reading all of this with interest. What you propose breaks a rule that you should not break. That is, if a field is to hold a date, let it hold a date... period. It should not hold a date AND/OR text. Therefore, this is OK:
/18/2004 (USA) or 18/1/2004 (UK)
This is not OK if you really want a date in the field:
"1/18/2004" or "18/Jan/2004" or "None" or "N/A"
What you would do is have a checkbox that is checked when you mean "N/A". If the checkbox is NOT checked, then the date textbox enables and you enter a real date there. If the N/A checkbox is checked then the date field is cleared to NULL.
If you enter a date but want it to show as 18/Jan/2004, then what you would do is in the FORMAT property of the date field you enter "d/mmm/yyyy". In that way, if someone types 18/1/2004, it will still display the way you want.
Again, don't mix datatypes in one field... in this case a true date and text.
gersheff
thank you greg.
think i get the gist of it. I will try when I am back at work.
But I am not sure what you mean by why some are ok and some aren't.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.