Export Query to *.txt file question

[mvorous]

Hello, I have to export a Query in txt form, "fixed width".
This is what I have as a text result...
2008 12 18 13 56 08

This is what I need for IT "no spaces" except between Year/day & time...
20081218 135608

How can I get the date to show like I need it?

Thank you in advance,
mvorous

 

[jwhite]

1. Is "text result" the output of your query?
2. Can you post your query?

 

[mvorous]

1. When I Run my Query actually, the date reads as 12/18/2008 4:14:12 PM
The output of my query "exported" in txt form is as shown below "what I have".

I cut out some of my original txt file for brevity, here it is in full with the SQL at the bottom...

What I have...
2233445 112233445 4302020 04 2008 12 18 0 00 00

What I need...
2233445 112233445 4302020 04 20081218 00000

2. SELECT PatientOT.[PT#], PatientOT.[MR#], ChargesAppliedOT.ReferenceNumber, ChargesAppliedOT.BillingTotal, Date() AS Expr1
FROM PatientOT INNER JOIN ChargesAppliedOT ON PatientOT.TableID = ChargesAppliedOT.TableIDTX
WHERE (((ChargesAppliedOT.ReferenceNumber)>1) AND ((ChargesAppliedOT.BillingTotal)>0));


Thanks, mvorous

 

[jwhite]

Try:

SELECT PatientOT.[PT#], PatientOT.[MR#], ChargesAppliedOT.ReferenceNumber, ChargesAppliedOT.BillingTotal, Format(Now(),"yyyymmdd hhnnss") AS Expr1
FROM PatientOT INNER JOIN ChargesAppliedOT ON PatientOT.TableID = ChargesAppliedOT.TableIDTX
WHERE (((ChargesAppliedOT.ReferenceNumber)>1) AND ((ChargesAppliedOT.BillingTotal)>0));

Changed Date() to Now(), as you seem to also want the Time, which Date() will not give you.

 

[mvorous]

Unfortunately, I tried Now(), but it left the spaces in as well.
Any other ideas?
mvorous

 

[Atomic Shrimp]

That's really weird - are you perhaps using a saved export specification that might be interfering with the output? When you just run the query and view the returned rows, do you see the spaces then?

 

[mvorous]

No, I deleted the saved specification name and have created many since then...

When I Run my Query actually, the date reads as 12/18/2008
The output of my query "exported" in txt form is
2233445 112233445 4302020 04 2008 12 18 0 00 00

What I need...
2233445 112233445 4302020 04 20081218 00000

Yep, weird

 

[Atomic Shrimp]

You could also try substituting the expression: Format(Now(),"yyyymmdd hhnnss") with:

Format(Now(),"yyyy") & Format(Now(),"mm") & Format(Now(),"dd") & " " & Format(Now(),"hh") & Format(Now(),"nn") & Format(Now(),"ss")

However, that's a really rather dirty method and ever so slightly prone to interesting failure - because by the time it finishes evaluating Format(Now(),"nn"), the seconds might have ticked back around to zero, resulting in data that looks like:
Expr1

20081219 161259
20081219 161200
20081219 161300

(a similiar thing could happen on any tick-over boundary, I guess, but it's unlikely on anything other than seconds)

 

[mvorous]

Mike
That did it, couldn't be happier!
I never would have thought of that restructuring the query format for date, you're the man, thanks again!

mvorous

 

[jwhite]

I'm just curious, why the first format example didn't work??? Did you not include what I gave before/after Now() ?

 

[mvorous]

Honestly, I was frustrated & probably didn't enter it in right, when I did get it, I never looked back. Your's looks cleaner, either way, thx for your input
mvorous

 

[Atomic Shrimp]

If fdcusa's suggestion works in your DB, you should definitely use it in preference to mine - because of the potential date boundary problems mentioned above.

If it doesn't work, it's a very interesting quandary...

 

[mvorous]

I went back and re-input it correctly, worked like a charm.
Thx all.