Date to Text yyyymmdd

[quinto via AccessMonster.com]

I have a form with a text box that I use to enter dates used by queries to
append information to tables and reports. I need to append a linked table
that all fields are in text (CSV) the date is in the following text format
yyyymmdd to limit the append to a date I added this to the query criteria but
I discovered that some errors are made and I would like to link this to the
date being displayed in the form, I created another text box and it displays
the date yyyymmdd but the upend query will not recognize the criteria as text
and the append is not happening. I need to convert the date displayed in the
form to text to cut one step and eliminate the date errors. Can it be done?
Thank you
Quinto

 

[Klatuu]

You can't update a linked csv file or a linked spreadsheet file.
The easiest way to handle this is to import the text file into a table, make
the changes, delete the old file, and export the table to the old file name.

 

[quinto via AccessMonster.com]

That is what I am doing but I have to select the date when promted by the
query criteria yyyymmdd
and what i am trying to do is to use the date already entered in the text
box in the form instead of entering the same date again in text format as
above. I was trying to use this date to generate a text box to read the date
and formatted in text that would match the field in the table being imported

You can't update a linked csv file or a linked spreadsheet file.
The easiest way to handle this is to import the text file into a table, make
the changes, delete the old file, and export the table to the old file name.

I have a form with a text box that I use to enter dates used by queries to
append information to tables and reports. I need to append a linked table

[quoted text clipped - 7 lines]

Thank you
Quinto

 

[Klatuu]

You could use:
Format(Forms!MyFormName!MyControlName, "yyyymmdd")
in the query.
--
Dave Hargis, Microsoft Access MVP

That is what I am doing but I have to select the date when promted by the
query criteria yyyymmdd
and what i am trying to do is to use the date already entered in the text
box in the form instead of entering the same date again in text format as
above. I was trying to use this date to generate a text box to read the date
and formatted in text that would match the field in the table being imported

You can't update a linked csv file or a linked spreadsheet file.
The easiest way to handle this is to import the text file into a table, make
the changes, delete the old file, and export the table to the old file name.

I have a form with a text box that I use to enter dates used by queries to
append information to tables and reports. I need to append a linked table

[quoted text clipped - 7 lines]

Thank you
Quinto

 

[quinto via AccessMonster.com]

I did use that and it works to convert the date format but it is still in
date format and it does not match the text format in the table being imported.
If I change that to text I get that crazy number in place of the date
I will play around a bit to see what alse I can come up with

Thanks

Quinto

You could use:
Format(Forms!MyFormName!MyControlName, "yyyymmdd")
in the query.

That is what I am doing but I have to select the date when promted by the
query criteria yyyymmdd

[quoted text clipped - 11 lines]

 

[Klatuu]

Sorry, I think I am confused.
I thought you had a date formatted as a date in a control on your form and
you wanted to export that date formatted as yyyymmdd.

If you can help me with a description of the process, I'm sure we can come
up with the correct solution. (sorry for my thickheadedness)
--
Dave Hargis, Microsoft Access MVP

I did use that and it works to convert the date format but it is still in
date format and it does not match the text format in the table being imported.
If I change that to text I get that crazy number in place of the date
I will play around a bit to see what alse I can come up with

Thanks

Quinto

You could use:
Format(Forms!MyFormName!MyControlName, "yyyymmdd")
in the query.

That is what I am doing but I have to select the date when promted by the
query criteria yyyymmdd

[quoted text clipped - 11 lines]

Thank you
Quinto

 

[quinto via AccessMonster.com]

I use an append query to import a CVS file, I have the following control to
enter the correct date ServDate: Forms!Operation!TripDate and this criteria
to select only part of the file, Like [Enter date in the following format
yyymmdd ] I want to do away for this prompt field and use the date entered in
the text box in the form to select the records that I want to import. I did
create another text box named AddDate and the control is =[Forms]![Operation]
![TripDate] for format I entered yyyymmdd and I added this to the query
criteria Like [Forms]![Operation]![AddDate] The date is being disp[lay
correctly yyyymmdd but nothing is imprted
Thanks again
Charles

You could use:
Format(Forms!MyFormName!MyControlName, "yyyymmdd")
in the query.

That is what I am doing but I have to select the date when promted by the
query criteria yyyymmdd

[quoted text clipped - 11 lines]

 

[Klatuu]

Are you linking to the csv file as a table?
If so, when you open the linked table and look at the field values, is it in
that format?
Is it importing as a text data type?
Does it work when you enter it from the prompt?

Sorry to ask so many questions, but without it in front of me to look at, it
is hard to tell what the problem might be because based on your description,
it should be working.

Another thing you might try is enter it as a regular date, then use the
Format function in the query
Format([Forms]![Operation]![AddDate], "yyyymmdd")

--
Dave Hargis, Microsoft Access MVP

I use an append query to import a CVS file, I have the following control to
enter the correct date ServDate: Forms!Operation!TripDate and this criteria
to select only part of the file, Like [Enter date in the following format
yyymmdd ] I want to do away for this prompt field and use the date entered in
the text box in the form to select the records that I want to import. I did
create another text box named AddDate and the control is =[Forms]![Operation]
![TripDate] for format I entered yyyymmdd and I added this to the query
criteria Like [Forms]![Operation]![AddDate] The date is being disp[lay
correctly yyyymmdd but nothing is imprted
Thanks again
Charles

You could use:
Format(Forms!MyFormName!MyControlName, "yyyymmdd")
in the query.

That is what I am doing but I have to select the date when promted by the
query criteria yyyymmdd

[quoted text clipped - 11 lines]

Thank you
Quinto

 

[quinto via AccessMonster.com]

The answer sare all yes, it works fine if I use the prompt, I need to find a
way to convert the long date to yyyymmdd in text, it does convert but If I
click on the field it shows as a short date if I click away it goes back to
yyyymmdd and that is the problem. I try to separate the num,ers by using mid
but it I stop because 01 will only display as 1
Charles

Are you linking to the csv file as a table?
If so, when you open the linked table and look at the field values, is it in
that format?
Is it importing as a text data type?
Does it work when you enter it from the prompt?

Sorry to ask so many questions, but without it in front of me to look at, it
is hard to tell what the problem might be because based on your description,
it should be working.

Another thing you might try is enter it as a regular date, then use the
Format function in the query
Format([Forms]![Operation]![AddDate], "yyyymmdd")

I use an append query to import a CVS file, I have the following control to
enter the correct date ServDate: Forms!Operation!TripDate and this criteria

[quoted text clipped - 16 lines]

 

[Klatuu]

Interesting. How about trying to compare both as formated.
That is, create a calculated field in your query that that is a version of
the date field formatted as yyyymmdd and compare that to the formatted
yyyymmdd of the text box.
--
Dave Hargis, Microsoft Access MVP

The answer sare all yes, it works fine if I use the prompt, I need to find a
way to convert the long date to yyyymmdd in text, it does convert but If I
click on the field it shows as a short date if I click away it goes back to
yyyymmdd and that is the problem. I try to separate the num,ers by using mid
but it I stop because 01 will only display as 1
Charles

Are you linking to the csv file as a table?
If so, when you open the linked table and look at the field values, is it in
that format?
Is it importing as a text data type?
Does it work when you enter it from the prompt?

Sorry to ask so many questions, but without it in front of me to look at, it
is hard to tell what the problem might be because based on your description,
it should be working.

Another thing you might try is enter it as a regular date, then use the
Format function in the query
Format([Forms]![Operation]![AddDate], "yyyymmdd")

I use an append query to import a CVS file, I have the following control to
enter the correct date ServDate: Forms!Operation!TripDate and this criteria

[quoted text clipped - 16 lines]

Thank you
Quinto