Creating an empty date / number field in a make table query.

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

Guest

I have Access 2000 and need to create 2 empty number fields and an empty date
field for data to be appended into later in the process. I'm sure this is
really simple but unfortunately I've not used Access for 5 years and have
forgotten some basics. I've tried using FieldName:Null, this creates a
binary field and also FieldName:"", which creates a text field. I'd
appreciate any help as this is driving me mad!!
 
I assume you mean in a Make Table query. Usually, you have to use an actual
date value like this: NewDate: #1/1/2000#.

But here's a way to trick it: NewDate: IIf("a"="b",#1/1/2000#,Null). Since
"a" will never equal "b", it always puts Null in the field, but it creates a
date field.

You can use
NewNumber: IIf("a"="b",1,Null)
to create a Long Integer field
and
NewNumber: IIf("a"="b",1.1,Null)
to create a Double precision field.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Roger Carlson said:
I assume you mean in a Make Table query. Usually, you have to use an actual
date value like this: NewDate: #1/1/2000#.

But here's a way to trick it: NewDate: IIf("a"="b",#1/1/2000#,Null). Since
"a" will never equal "b", it always puts Null in the field, but it creates a
date field.

You can use
NewNumber: IIf("a"="b",1,Null)
to create a Long Integer field
and
NewNumber: IIf("a"="b",1.1,Null)
to create a Double precision field.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...

Hi, I know this is a 12 year old thread, but I was so impressed with your solution that I joined this website just to thank you for your contribution.

I had the same question as OP, and was struggling to figure out how to change all of the blank columns for the 94 tables I created with Make Table queries into a number data type and not a text/binary column.

Your solution is so elegant - thank you so much!
 
Back
Top