Binary field won't take dates

[aleon]

How do you create a binary field for dates, when you are going to update or
append that field? I've tried using eg. "stop_date: null" or "stop_date: 0"
in the make table query that creates the field, but when I run my update
query to add the date, it puts either just numbers or strange characters. I
can't find info in the knowledge base that tells me how to create a date
binary field. Thanks for your help.

 

[Klatuu]

To my knowledge, there is not such thing. What are you trying to do?

 

[aleon]

I'm creating a table from a make table query. In that query, I'm adding a
field called "stop_date". Then I'm running an update query to add the dates
to the stop_date field. When I create the field in the original make table
query using "stop_date:null" or "stop_date:0", the table called the field a
binary field. When I try to update this field with the dates, it puts only
numbers or strange characters into the table field "stop_date".

 

[Rick Brandt]

I'm creating a table from a make table query. In that query, I'm
adding a field called "stop_date". Then I'm running an update query
to add the dates to the stop_date field. When I create the field in
the original make table query using "stop_date:null" or
"stop_date:0", the table called the field a binary field. When I try
to update this field with the dates, it puts only numbers or strange
characters into the table field "stop_date".

So change those fields from binary to DateTime. If you will be constantly
running this MakeTable query (not a good idea) then you need to supply date
values to the MakeTable query if you expect it to create DateTime fields.

 

[Klatuu]

I think you need two fields, one for the date which will be a date data type
and another for the criteria.
Where is the source data for the make table query coming from. I really
don't like make table queries for a number of reason. I would suggest you
use an append query. This way you can defne the field sizes and data types.

 

[Gary Walter]

How do you create a binary field for dates, when you are going to update
or
append that field? I've tried using eg. "stop_date: null" or "stop_date:
0"
in the make table query that creates the field, but when I run my update
query to add the date, it puts either just numbers or strange characters.
I
can't find info in the knowledge base that tells me how to create a date
binary field. Thanks for your help.
--

Hi Aleon,

One clever workaround is to use
an IIF construct in your make table
query as Michel once demonstrated:

NewField: IIf(True,Null,#1/1/1900#)


-- you end up with date/time field, all nulls


-- works for text and number also
NewField: IIf(True,Null," ") <--get type text(255), all null
NewField: IIf(True,Null,0) <--get type Long, all null


-- or try Cxxx functions
NewField: IIf(True,Null,CCur(0)) <--get type Currency, all null
NewField: IIf(True,Null,CDbl(0)) <--get type Double, all null


So clever...and eliminates "binary type" bugaboo
when you had used


NewField: Null


Good luck,

gary

 

[aleon]

Gary, that is awesome! I knew it could be done! I'll use this for sure.
Thanks for your expertise.

 

[Buddy_San_Diego]

Gary,

I have a similar problem and I'm trying to figure out how to apply your
solution.

I have a make-table query, with the following expression:
Cap_month: [CapMonth]
The user would enter a date 6/1/2007, however, the new (make table) shows
the value as binary. I'm trying to have it be a date. I'm a beginner to
moderate user and I can't seem to take your solution to my problem. Can you
help?

Buddy

 

[Gary Walter]

I have a similar problem and I'm trying to figure out how to apply your
solution.

I have a make-table query, with the following expression:
Cap_month: [CapMonth]
The user would enter a date 6/1/2007, however, the new (make table) shows
the value as binary. I'm trying to have it be a date. I'm a beginner to
moderate user and I can't seem to take your solution to my problem. Can
you
help?

Buddy

Hi Buddy,

If you are saying "[CapMonth]" will request a date
from the user, then I believe you just need to

explicitly set the parameter type.

In top menu (not sure about 2007),
click on Query/Parameters

fill in dialog box

Parameters DataType
[CapMonth] Date/Time

you could try the IIF-cast method,
but *above method is "correct way"*...

Cap_month: IIF(True, [CapMonth],#1/1/1900#)

good luck,

gary

 

[John W. Vinson]

Gary,

I have a similar problem and I'm trying to figure out how to apply your
solution.

I have a make-table query, with the following expression:
Cap_month: [CapMonth]
The user would enter a date 6/1/2007, however, the new (make table) shows
the value as binary. I'm trying to have it be a date. I'm a beginner to
moderate user and I can't seem to take your solution to my problem. Can you
help?

PMFJI... you can use

Cap_Month: CDate([CapMonth])

Access is probably interpreting 6/1/2007 as an arithmetic division operation
and storing the result.

John W. Vinson [MVP]

 

[Buddy_San_Diego]

Thanks. It worked.

I have a similar problem and I'm trying to figure out how to apply your
solution.

I have a make-table query, with the following expression:
Cap_month: [CapMonth]
The user would enter a date 6/1/2007, however, the new (make table) shows
the value as binary. I'm trying to have it be a date. I'm a beginner to
moderate user and I can't seem to take your solution to my problem. Can
you
help?

Buddy

Hi Buddy,

If you are saying "[CapMonth]" will request a date
from the user, then I believe you just need to

explicitly set the parameter type.

In top menu (not sure about 2007),
click on Query/Parameters

fill in dialog box

Parameters DataType
[CapMonth] Date/Time

you could try the IIF-cast method,
but *above method is "correct way"*...

Cap_month: IIF(True, [CapMonth],#1/1/1900#)

good luck,

gary