473,326 Members | 2,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Maintain data type in make table query

I have a make table query. In this query I have created several fields that will be empty and will be updated or appended to from other tables. the problem I am having is that when the table is created some of the fields become short text. how do I make sure that they become date/time data type in the new table. I don't want to have to open the table in design view and change the data type.
Jun 9 '17 #1
2 2128
Luuk
1,047 Expert 1GB
Can you post the 'make table query'?

If it is something like:
CREATE TABLE TEST1 SELECT a,b,c FROM TEST2

Then, if one of the values in table TEST2 is NULL, the data type of that column in table TEST1 might be of a different type
Jun 10 '17 #2
Silly me, I have been working in excel for some time and when I was creating my Make table query I was trying was pulling in a field that has dates, but not all records have dates in that field. so I created an expression that essentially would handle null results in the field. thinking in excel terms if you use a end of month formula, if the field is null you get an erroneous answer. so in my end of month expression I put iif(is null([field])=true,"",end of month expression)) this converted the field to text in the new table. I then removed the iif statement and just used the end of month expression, and the field remained a date/time field in the new table, the blank records just came over as blank records.
Thank you for your response, I just have to remember not to be thinking in terms of excel while in access.
Jun 14 '17 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Oreo Bomb | last post by:
I have a secured database that contains a Read-Only group. This group has permissions to view reports, but cannot add, edit, or delete any DB objects. One of the reports the group needs access to...
2
by: Kathy Krizl | last post by:
I'm probably doing something stupid, but I have a make table query. One of the tables I reference has some check box fields in it. Their Data Type is Yes/No, their field property format is Yes/No,...
2
by: Doc | last post by:
Per earlier post, I am trying to save 'out' production data from a program called Solomon - basically (I think) this was /is an Access/Sql based program. We are updating to different application...
4
by: ken | last post by:
Hi, I use this command to run a make table query without opening it... CurrentDb.Execute "make table query name" Access tells me that it can't execute a select query...? Its a make table query...
6
by: Laetitia Clark | last post by:
Is it possible to force a Decimal type field from an ODBC source to become a Double type field in the destination table when using a Make Table query ? Thanks in advance. Laetitia South...
3
by: Robertf987 | last post by:
Hi, I'm a bit stuck with an access database. I'm using access 2000 if that's any help. Right, it's 3:40am right now and I'm rather tired, but I *hope* this makes sense. I have a table which...
1
by: arcingmad | last post by:
Hi I am using the following code in a button click event to run a query stored in a .mdb file from an external geographic program ArcGIS via a jet adodb connection I have the need to run an...
1
by: Simon | last post by:
Dear reader, In a Make-Table query the TickBox from the master table transfers in to a binary value in the new table. How can I force the Make-table query to keep the TickBox in the new...
9
by: Mourad | last post by:
Hi All, Is it possible to create a Make Table query in access (2.0 and 2003) that creates the table into a SQL Server database? Following the steps: 1- Create New Query 2- Set Query Type as...
1
by: Mihail | last post by:
Hi all ! What I have: In a BE database I use a temporary table to store some data. Of course, for different filters that data will be different. Keep in mind, please, that the table structure is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.