Create module to add field

[DavidAlan]

I have a table (IncdSummary) with a poorly formed date field ([DISPTIME]which has a date plus junk). The following query gets me the date I need:

SELECT Trim(Left([DISPTIME],InStr([DISPTIME]," "))) AS [Date], CDate([Date]) AS MyDate, DatePart("m",[Mydate]) AS DM, DatePart("yyyy",[Mydate]) AS DY, DatePart("d",[Mydate]) AS DD, [DY] & Format([dm],"00") & Format([dd],"00") AS DateNumeric


FROM IncdSummary;

This takes a value of 7/6/2010 00:12:15pm and returns 20100706 as a numneric value as needed.

Can I use this in a module to add a numeric field to IncdSummary table. I have no idea how to create of format a module.

[pbaldy]

I'm not clear on what you're trying to achieve. I assume you don't actually want to add a new field to a table. I'd guess you want a function that accepts the poorly formatted field as an input and returns the numeric value. That would be fairly simple. This should give you a start on the function:

http://www.baldyweb.com/Function.htm

In your function, you would just use one or more variables to keep track of your values as you processed them. In fact, you could probably just get to the CDate() part, then use the Format() function:

Format(DateValueHere, "yyyyddmm")

[DavidAlan]

actually DO want to add the field to the table (as a numeric field) - data is eventually imported into an ARC GIS .shp file and has to be queried thru an ARC web app. Have to query as a numeric field in order to accomplish what is needed. Seems the simplest way would be to import the clean data as needed rather that import the squirrelly stuff into ARC, then manipulate thru GIS.

[pbaldy]

Can't you just add the field in table design view once and be done with it? It's unusual (but doable) to add fields in code. If you really need to do it through code, you can either execute a DDL query:

ALTER TABLE...

or use a DAO TableDef in VBA code.