Setting the data type of a new filed in a make table query

[Jasonm]

Is there a way to implicitly set the data type of a new filed in a make
table query? My sql is below, and I need the to set several of the fileds to
single or double, but they are being created as long integer...

What do I need to do to get this to funciton? I have searched several NG's
with no success and have not found the answer in the help file.

Thanks in advance, Jason

SELECT Equipment.ID, Equipment.CategoryID, Equipment.HourReading,
Max(Hours.Hours) AS MaxOfHours, 0 AS NewHours, Equipment.Description, Date()
AS NewDate INTO tblHoursEntryPS
FROM Equipment LEFT JOIN Hours ON Equipment.ID = Hours.EquipmentID
GROUP BY Equipment.ID, Equipment.CategoryID, Equipment.HourReading, 0,
Equipment.Description, Date()
HAVING (((Equipment.CategoryID)=7) AND ((Equipment.HourReading)=True));

MaxOfHours comes in as a double because Hours.Hours is a couble, but
NewHours starts out as a long integer and I need it to be a Double... Thanks
again for your help

 

[John Vinson]

Is there a way to implicitly set the data type of a new filed in a make
table query? My sql is below, and I need the to set several of the fileds to
single or double, but they are being created as long integer...

Not very easily. You can use the CDbl() function.

What do I need to do to get this to funciton? I have searched several NG's
with no success and have not found the answer in the help file.

Thanks in advance, Jason

SELECT Equipment.ID, Equipment.CategoryID, Equipment.HourReading,
Max(Hours.Hours) AS MaxOfHours, 0 AS NewHours, Equipment.Description, Date()
AS NewDate INTO tblHoursEntryPS
FROM Equipment LEFT JOIN Hours ON Equipment.ID = Hours.EquipmentID
GROUP BY Equipment.ID, Equipment.CategoryID, Equipment.HourReading, 0,
Equipment.Description, Date()
HAVING (((Equipment.CategoryID)=7) AND ((Equipment.HourReading)=True));

MaxOfHours comes in as a double because Hours.Hours is a couble, but
NewHours starts out as a long integer and I need it to be a Double... Thanks
again for your help

Two questions:

Why does it need to be a Double?

Deeper question: WHY do you need to do a MakeTable AT ALL?

You're creating a new table which - apparently - contains only data
which is already stored in your database, or which can be readily
derived from data that you already have.

You may be assuming (based on experience with other database software
such as dBase) that you need to create a new Table to do much of
anything with data. This assumption is *wrong*. You can use a Select
Query as the basis for a Form, a Report, even another Query; it is not
necessary to create a new Table to do so.

John W. Vinson[MVP]

 

[Jasonm]

John, Thank you for you reply. I have created a small database that has
grown into a larger database and bits and pieces don't always fit together
like I would like...

Let me fall back a bit and explain WHY I am creating the table in the first
place. I have a table that stores hour readins for various pieces of
equipment. Weekly we generate a data collection form for a user to go out
inot the field get new hour readings and then enter those readings into the
database.

In an effort to make the entry form easier to use, and more effective I
wanted to populate the form with all of the equipment that needed an hour
reading, display the LAST reading (for comparison to the new data) and add
the current date.

The best that I could come up with at the time was to create the table, have
the user enter the NEWHOURS and NEWDATE into the table and then append the
table to the main table. I knew it was klunky, but I couldn't find a better
method at the time... (I am still fairly new to this). If you think this is
possible with a Select query, or if I can provide you with some additional
information to point me in the right direction I would be very appreciative.

The keys for me are populating the form with ONLY the equipment that need
hour readings, displaying the LAST reading, Providing a spot for the current
reading date and value and then appending that data to the main table.

Thanks for taking the time to help me with this problem. I will work on a
query(or VBA) to accomplish this in another way while I wait for an
additional suggestion.

Jasonm