Changing .DAT to .CSV to Automatically Import into Tables

[ssanfu]

Jay,
First..... Do yourself a favor. You must have a better naming scheme - NO spaces, special characters or punctuation!
That means ONLY letters, numbers or the underscore.

Example of a bad name: [CXR Image D/T] - has a space and slash.
Good/better names:
[CXRImageDT]
[CXR_Image_D_T]
[CXR_Image_DT]

Only the programmer will/should see the names, so correct English is unnecessary. And if you ever decide to use SQL Server Express, SQL Server, MySQL,etc you will have to do a massive rename because the "Big Boy" Engines do not allow spaces, special characters or punctuation in names.



OK, Step 1. Create a new query (TestSS1) using the following:

SELECT DISTINCT [ITXM BLOOD Data].MRN, Format([IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime],"mm/dd/yy hh:nn") AS [CXR Image D/T]
FROM ([ITXM BLOOD Data] INNER JOIN [IMGC IMAGE Data] ON [ITXM BLOOD Data].MRN = [IMGC IMAGE Data].MRN) INNER JOIN ITxM_Data ON [IMGC IMAGE Data].MRN = ITxM_Data.MRN
WHERE ((([ITXM Blood Data].[IssuedDate]) Between Date()-2 And Date()-1) AND (([IMGC IMAGE Data].[ImageDate]) Between Date()-2 And Date()-1) AND ((DateDiff("n",[ITXM BLOOD Data].[IssuedDate],[IMGC IMAGE Data].[ImageDate])) Between 0 And 751))
ORDER BY [ITXM BLOOD Data].MRN, Format([IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime],"mm/dd/yy hh:nn");

Execute this query, then execute your current query. The returned data sets should be identical.
Let me know.


You can also try this sort order:

ORDER BY [ITXM BLOOD Data].MRN, [IMGC IMAGE Data].[ImageDate], [IMGC IMAGE Data].[ImageTime];

[jjz0512]

Steve, I know, I know, about the naming convention. It's a old habit. Tried your query, ad it returns a little too much data. you're missing the time of day in the criteria. Column [ITXM Blood Data].[IssuedDate]+[ITXM Blood Data].[IssuedTime] criteria is Between Date()-2+#12:00:00 PM# And Date()-1+#11:59:00 PM#. That's important as it only looking for a 36 hour window. Column [H1PD XRAY Data].[OrderDate]+[H1PD XRAY Data].[OrderTime] criteria is - Between Date()-1+#12:01:00 AM# And Date()-1+#11:59:00 PM#. That's important as it only looking for a 24 hour window. My original query is working fine.

But, what I need help with, is changing the first Date() and second Date() to a parameter, so I can processing more than a specific date, which I do manually by changing the -2 & -1 values. I also nees this to be calculated in the DateDiff column.

[ssanfu]

I missed the fact that it was noon instead of midnight. But that is why testing is so important.


Create (or use a current form) and add a text box.
I set up the text box:
Tab Other - NAME = "TheDate"
Tab DATA -Input Mask = 99/99/0000;0;_
Tab FORMAT - Format = Short Date

Replace "Date()" with "Forms!FormName.TheDate" (but replace "FormName" with your form name)
Note that the form must be open when the query is executed.

Note the parenthesis:

Between ([forms]![Form2].[TheDate]-2)+#12/30/1899 12:0:0# And ([forms]![Form2].[TheDate]-1)+#12/30/1899 23:59:0#)

This is the query

SELECT DISTINCT [ITXM BLOOD Data].MRN, Format([IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime],"mm/dd/yy hh:nn") AS [CXR Image D/T] FROM ([ITXM BLOOD Data] INNER JOIN [IMGC IMAGE Data] ON [ITXM BLOOD Data].MRN = [IMGC IMAGE Data].MRN) INNER JOIN ITxM_Data ON [IMGC IMAGE Data].MRN = ITxM_Data.MRN WHERE ((([ITXM Blood Data].[IssuedDate]+[ITXM Blood Data].[IssuedTime]) Between ([forms]![Form2].[TheDate]-2)+#12/30/1899 12:0:0# And ([forms]![Form2].[TheDate]-1)+#12/30/1899 23:59:0#) AND (([IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime]) Between ([forms]![Form2].[TheDate]-2)+#12/30/1899 23:45:0# And ([forms]![Form2].[TheDate]-1)+#12/30/1899 23:59:0#) AND ((DateDiff("n",[ITXM BLOOD Data].[IssuedDate]+[ITXM BLOOD Data].[IssuedTime],[IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime])) Between 0 And 751)) ORDER BY [ITXM BLOOD Data].MRN, Format([IMGC IMAGE Data].[ImageDate]+[IMGC IMAGE Data].[ImageTime],"mm/dd/yy hh:nn");

Just enter the correct date in the text box "TheDate" and execute the query......

I don't know how 12/30/1899 12:0:0# gets in there.

Date stuff
---------------
When you enter only a date into a Date/Time field, the Time component defaults to "00:00:00" (aka midnight)
When you enter only a time into a Date/Time field, the Date component defaults to the earliest date, which MS set at "12/30/1899"

Access has a field type "Date/Time", which is actually a double precision number. Access displays the number as a date/time.

The number to the left of the decimal is the number of days from the beginning date (remember the "12/30/1899"??). The number to the right of the decimal is the portion of the day. So noon (12:00PM) is 12/24, which is .5 of a day. Or if you want to use minutes, a day has 1440 minutes, therefore noon is 720 minutes of a day.
If you want to know what portion of a day 11:59PM is, use 1439/1440. This is approx 0.999305.
11:45PM is approx 0.989583 of a day.

Why is this important? You can do date math: if you want to add one day to a date you can use Date()+1.
If you want to add half a day (12 hours), you can use date()+ 0.5.

So, instead of

Between ([forms]![Form2].[TheDate]-2)+#12/30/1899 12:0:0# And ([forms]![Form2].[TheDate]-1)+#12/30/1899 23:59:0#)

you can/could use

Between ([forms]![Form2].[TheDate]-2)+ 0.5 And ([forms]![Form2].[TheDate]-1)+ 0.999305)