Need help with a Query (Access 97)

Topic

New Reply

Morning!!

I would like to know if there is a way to pull out the date information from FileID, in “qryReceived”, and put that date into another column in the same query.

Thanks

Reply | Quote

Replies

Yopu could create a calculated column:

TheDate: CDate(Mid([FileID],45,6))
or
TheDate: DateValue(Mid([FileID],45,6))

The File column can also be created using the following expression:

File: Mid([FileID],58,5)

Note: the FileID field in tblReceived looks as if the table is derived from a fixed-width text file. You can use File | Get External Data | Import… to import a text file and specify how to divide the lines of the text file into columns. That is much easier than extracting information later on.

Reply | Quote

Thanks so much.

I am trying to find an example of this in an Access book. I do see some information on the TransferText, but am not quite sure what to use for the SpecificationName.

Reply | Quote

First, import the file “manually” by selecting File | Get External Data | Import…When you have specified the field breaks, field names, field types etc.., and have arrived at the last step of the Import Text Wizard, click Advanced… You will see an overview of the settings. Click Save As…. to save these settings as an import specification; you can accept the name proposed by Access or replace it by a name of your choice. You can use the name of the specification later, when you want to import the table programmatically, using DoCmd.TransferText.

Reply | Quote

First, import the file “manually” by selecting File | Get External Data | Import…When you have specified the field breaks, field names, field types etc.., and have arrived at the last step of the Import Text Wizard, click Advanced… You will see an overview of the settings. Click Save As…. to save these settings as an import specification; you can accept the name proposed by Access or replace it by a name of your choice. You can use the name of the specification later, when you want to import the table programmatically, using DoCmd.TransferText.

Reply | Quote

Thanks so much.

I am trying to find an example of this in an Access book. I do see some information on the TransferText, but am not quite sure what to use for the SpecificationName.

Reply | Quote

Yopu could create a calculated column:

TheDate: CDate(Mid([FileID],45,6))
or
TheDate: DateValue(Mid([FileID],45,6))

The File column can also be created using the following expression:

File: Mid([FileID],58,5)

Note: the FileID field in tblReceived looks as if the table is derived from a fixed-width text file. You can use File | Get External Data | Import… to import a text file and specify how to divide the lines of the text file into columns. That is much easier than extracting information later on.

Reply | Quote