Sort a continuous form by the "TXDate" column (yes, dates) in ascending order

[brucemc777]

After reviewing some of the works out there i feel really stupid asking this question; i intend on going back and reviewing some of them because they provide amazing results.

All i want to do is sort a continuous form by the date column in ascending order, probably because i am too new and too confused as to how to make the following SQL statement include and ORDER BY, for if i could do that then after adding an item i could simply use "requery" (right?)-

    CurrentDb.Execute "INSERT INTO tblTransaction (IDCustomer, IDAgreement, TXDate, IDTXType, TXPmtDebit, TXPmtCredit, TXTaxDebit, TXTaxCredit, TXNotes, TXTimeStamp)" & _
                       "VALUES(" & lCustomer & ", " & lAgreement & ", #" & dtDate & "#," & lType & ", " & dPmtDebit & ", " & dPmtCredit & ", " & dTaxDebit & ", " & dTaxCredit & ",""" & sNotes & """,#" & dtNow & "#);"

and i promise you i wouldn't be posting unless i felt like i was just beating my head against a wall and lost in a forest. Of walls. A big one. No trees.

Thank you.

My head hurts.

 

[brucemc777]

Uhg. I just found my answer. Not setfilter, but SetOrderBy.

Still, if anyone can advise how to correct that SQL i highly would appreciate the help!!!

 

[The_Doc_Man]

"correct that SQL" ...

What is wrong with it? What results were you expecting and didn't get, or what were you NOT expecting and DID get?

If the TXDate field is text and dtDate is a date variable, your problem may be one of mixed data type. Please clarify the exact data types involved.

 

[brucemc777]

My issue is that every time i try to then add "ORDER BY" to the SQL statement i fail. I can't make that work! I want to Order By TXDate ASC

TXDate is "Date/Time" in the table by design

If i run:

          DoCmd.SetFilter wherecondition:="CDate(TXDate) BETWEEN #" & Format(Forms!frmPaymentData.tbMinDate.Value, "yyyy-m-d") & _
                                           "# AND #" & Format(Forms!frmPaymentData.tbMaxDate.Value, "yyyy-m-d") & "#"

on it where tbMinDate and tbMaxDate (you already know, but so i don't make a dumb assumption, tbMinDat and tbMaxDate are text boxes wherein the user can enter minimum and maximum dates to display) the code runs as expected-

Thank you The_Doc_Man!

 

[The_Doc_Man]

The question is formatting.

When you have a variable or field that is of data type DATE (also written as DATE/TIME, same meaning), it is a number used to represent the days since the "epoch" date (the date at which, for practical purposes, time is assumed to have begun; the time that, when translated, equals 0.00000 in the internal Access scale.) For Access, that date is midnight 30-Dec-1899. There's a long story as to why THAT date. I'll spare you the details for now. So... when you have this in your first query that you showed us,

lAgreement & ", #" & dtDate & "#," & lType & ",

IF dtDate is a DATE variable you will get ..."# <some number> #, ... and that isn't what you wanted. You PROBABLY wanted

lAgreement & ", #" & FORMAT( dtDate, "ShortDate" ) & "#," & lType & ",
    or
lAgreement & ", #" & FORMAT( dtDate,"dd-mmm-yyyy") & "#," & lType & ",

The presence of the octothorpes (#) is a signal to Access that the text delimited on either side by that special character will be a DATE STRING. That lowly hash-tag symbol is what is called a "type hint" or type marker. For BASIC, type hints include $ for strings, % for integers, 0h for a hexadecimal string, and surrounding octothorpes for date string. There are others. But then there is the "Doc" hint - instead of using type hints, just DIM the variable in the right type and forget about most of the type hints.

HOWEVER, there is another way to look at it. What is the data type of TXDATE? If it is a text-oriented date AND you want to later sort it, then you need this:

lAgreement & ", '" & FORMAT( dtDate, "yyyy-mm-dd" ) & "'," & lType & ",

... and watch out for the presence of an apostrophe in the quoted strings in the last example. The reason is that if you want to sort a text field, you have to format that field to properly support the sort. Which means that dates have to be presented as I have shown, as year, numeric month, and day IN THAT ORDER. If TXDATE actually is a string containing a formatted date, it will sort according to text-sorting rules.

You have an either-or situation here. Know which data type you REALLY want because unless you want to have duplicated date fields, one in numeric and one in string form, you will need to recognize the difference between a computational value and a display value. You may ask, "But why would I want dates as a computational value?" To use various date-related functions like DateDiff to find the difference between two dates; DateAdd to provide an offset past or future date; or FORMAT to show the date in some other unusual format; or sorting, because the numeric-form dates always sort correctly regardless of any format in which you might choose to show those dates. Further, date math is pretty easy most of the time when staying in numeric format.

EDIT: Just noticed on second reading that TXDATE is a date field. Therefore, the formatted ShortDate case or "dd-mmm-yyy" case in octothorpes should do the trick.

 

[brucemc777]

I'm looking forward to working with this tomorrow when i can focus a little better (just tired), but you hit another nerve - for a while i was trying to format dtNow at the end of the VALUES group-

& Format(dtNow, "mm/dd/yyy hh:nn:ss" &

and after typing i looked at it again, saw the quotation marks and realized how much more i was complicating my nightmare! One battle at a time-

Thank you, i really appreciate your guidance. As my doctor used to say to me some 35 years ago, "Well Bruce, you are not getting any younger"...

 

[The_Doc_Man]

I don't know if I'm the cause or not, but in the last five years at least seven of my general practitioners or specialists have retired. Apparently they weren't getting any younger either.

 

[cheekybuddha]

INSERT queries don't have an ORDER BY clause.

Just use the ORDER BY in your subsequent SELECT statement when you are trying to retrieve your data.

 

[plog]

All i want to do is sort a continuous form by the date column in ascending order

Agree with cheeky, that INSERT query has nothing to do with how data is displayed on your form. If you want to sort use DoCmd.SetOrderBy:

DoCmd.SetOrderBy method (Access)

Office VBA reference topic

learn.microsoft.com

Further, why do you have an INSERT query? Why aren't you using bound forms?

Even further, the fields of your table have a few red flags:


1. TXDate and TXTimeStamp. Why are your times and dates different? It's a date/time field it can handle them both, why do you have 2 seperate fields?

2. _Debit and _Credit fields. Why are your debits and credits in different fields? If you need a balance this is going to bite you. You simply put both credits and debits in the same field and then just add that field up to get your balance.

 

[brucemc777]

Last nite around 2:00am it came to me that i thought i once learned that an INSERT INTO did not use an ORDER BY; glad that was verified today, you saved me from going on another exploration. Had been using the DoCmd.SetOrderBy but kept thinking that this was because i wasn't getting the syntax right!

@ plog: In this instance the INSERT INTO is nested in a For/Next to initialize usually 60 entries. I understand and have considered that which you state about separate debit/credit fields. I have thought it over too many times and for display clarity i feel at this point it would be best. Of course, that could change with the wind- TXDate is the date a future payment is due, incremented monthly where the TXTimeStamp is just a weak secrity checkpoint.

 

[cheekybuddha]

for display clarity i feel at this point it would be best.

You should [almost] never let how you wish to display your data dictate how you store your data.

 

[brucemc777]

You should [almost] never let how you wish to display your data dictate how you store your data.

I will keep that in mind - this is the first Access project i've done in over ten years. A lot of re-learning! And now going between Access and Excel really messes me up...

 

[brucemc777]

The question is formatting.

When you have a variable or field that is of data type DATE (also written as DATE/TIME, same meaning), it is a number used to represent the days since the "epoch" date (the date at which, for practical purposes, time is assumed to have begun; the time that, when translated, equals 0.00000 in the internal Access scale.) For Access, that date is midnight 30-Dec-1899. There's a long story as to why THAT date. I'll spare you the details for now. So... when you have this in your first query that you showed us,

lAgreement & ", #" & dtDate & "#," & lType & ",

IF dtDate is a DATE variable you will get ..."# <some number> #, ... and that isn't what you wanted. You PROBABLY wanted

lAgreement & ", #" & FORMAT( dtDate, "ShortDate" ) & "#," & lType & ",
    or
lAgreement & ", #" & FORMAT( dtDate,"dd-mmm-yyyy") & "#," & lType & ",

The presence of the octothorpes (#) is a signal to Access that the text delimited on either side by that special character will be a DATE STRING. That lowly hash-tag symbol is what is called a "type hint" or type marker. For BASIC, type hints include $ for strings, % for integers, 0h for a hexadecimal string, and surrounding octothorpes for date string. There are others. But then there is the "Doc" hint - instead of using type hints, just DIM the variable in the right type and forget about most of the type hints.

HOWEVER, there is another way to look at it. What is the data type of TXDATE? If it is a text-oriented date AND you want to later sort it, then you need this:

lAgreement & ", '" & FORMAT( dtDate, "yyyy-mm-dd" ) & "'," & lType & ",

... and watch out for the presence of an apostrophe in the quoted strings in the last example. The reason is that if you want to sort a text field, you have to format that field to properly support the sort. Which means that dates have to be presented as I have shown, as year, numeric month, and day IN THAT ORDER. If TXDATE actually is a string containing a formatted date, it will sort according to text-sorting rules.

You have an either-or situation here. Know which data type you REALLY want because unless you want to have duplicated date fields, one in numeric and one in string form, you will need to recognize the difference between a computational value and a display value. You may ask, "But why would I want dates as a computational value?" To use various date-related functions like DateDiff to find the difference between two dates; DateAdd to provide an offset past or future date; or FORMAT to show the date in some other unusual format; or sorting, because the numeric-form dates always sort correctly regardless of any format in which you might choose to show those dates. Further, date math is pretty easy most of the time when staying in numeric format.

EDIT: Just noticed on second reading that TXDATE is a date field. Therefore, the formatted ShortDate case or "dd-mmm-yyy" case in octothorpes should do the trick.

I hope i can get comfortable with handling the quotation marks and octothorpes (you sure you didn't make that word up??? for string, long and date types-

 

[Pat Hartman]

In this instance the INSERT INTO is nested in a For/Next to initialize usually 60 entries.

Why? Do you actually have this insert inside a VBA loop that is reading some other table?????????? If so, you are running 60 SEPARATE insert queries when you could be running one if you used an append query that copied the data from the source table and appended the rows using a variable as the FK. ONE query? SIXTY queries??

I have thought it over too many times and for display clarity i feel at this point it would be best.

Separate Debit and Credit fields are an artifact from when accountants wore green eyeshades and worked in ledger books. Addition is easier than subtraction so they could add two long columns and then do one subtraction. You can properly normalize the table by keeping a single column but still print two columns in your reports if the user prefers it.

 

[brucemc777]

@ Pat Hartman - Maybe the rattler did bite me...

I'm afraid you might be asserting an understanding on how to do this more efficiently that is way over my head, or at least above my consideration of translating the final presentation to the tables thus far as cheekybuddha poignantly indicated.

What is happening is that there is an initial form where the basic information is gathered, items such as payment amount, number of payments, tax %, .... The routine we are discussing runs once at the initiation of the agreement to set the table up. Over time (generally 60 months) after that incoming payments are logged to it, it is noted for any missed payments, - account maintenance sort of use. All account agreements are logged to the same table with their entries identified by the account number. This was the only model that occurred to me. The initial setup run of the INSERT INTO takes about a fraction of a second, yet if there is a better way i would like to learn.

And why are you making jokes about my green eyeshades, how did you know? One crack about my pocket protector and i'm outta here.

Combining that which both you and everyone else in the known universe have stated, i see one column with positive and negative values, the application of which are identified already by the Type column, and for display and reporting purposes could be separated into Debit and Credit columns by their respective values, and at a higher level by their type values. Would this be the proper application of the concept you have in mind?