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-

 

[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?

 

[brucemc777]

Will need to add a dimension to thinking this through that i hadn't thought of before, as up until now it was strictly to solve for the output as presented, but now there is a new allocation that i must think through. It would not be hard to convert, but it would take some verification of details.

The user never touched the tables (theoretically), just the GUI forms, which provide for both input and display. The display was built to approximate a rudimentary Excel spreadsheet they have been using, which laid out the 60 monthly payments per customer who went on a contract as let's say a kind of check-list of payments to be made, against which payments made would be logged. Running balances would also be kept to account for shortfalls or over-payment at any point in time.

If i went to a single currency field, the field for the "Transaction Type" combined with whether the entry was positive or negative would provide for proper accounting, display and reporting separation. That would take some "busy work" but nothing really technical that i would have to sort out. In doing so i could combine payment credit and debits (which presently includes all fund related to regular contracted payments and related charges such a "late charge", reversal due to NSF or forgiveness of a charge, property tax assessment,... , and the other separate debit & credit fields for Use/Sales Taxes into one single field. As to the idea of a "check-list of payments" i could create a control that displayed how many of the 60 (or whatever other contracted amount) of payments had been paid with I suppose a "next due" which could simply be constructed from the last scheduled payment plus one month.

The displays could remain the same, "simply" the programmed interface would change.

Am i on the right track, and am i missing anything?

Many thanks,
-Bruce

 

[The_Doc_Man]

Am i on the right track, and am i missing anything?

1. If you are attempting to clarify, simply, or improve what goes on behind-the-scences then you are on a good track. Particularly if this will make future maintenance easier or if it will allow future upgrades, then you are not doing yourself a disservice at all. But understand that from the sidelines we can only encourage you. Unfortunately, you still have to do the work yourself. There IS considerable merit to the idea of keeping the same "look and feel" but with some kind of internal improvements. That specific goal is an EXCELLENT path because it shows that your users are a big part of your priorities. In which case they are lucky you are there for them.

2. If you are human and still breathing, you are missing something - though we don't necessarily know what. We ALL miss something, so don't freak out when you discover that you DID miss something. Just carry on.

 

[brucemc777]

Many thanks - Though i find i do have memory problems (me, not my computer) i want to learn to do this right. This is simply an gratis program for some friends; sooner or later i want to convert an Excel monster that that accounts for my personal business which i have been adding to since its start in 2016 to Access, which is what it should have originally been created with!

 

[Mark_]

For myself, I'd never pre-build the transactions. I'd use a query that calculates the total amount paid and the next payment date. I also wouldn't break out tax in the individual transactions as that should be in the parent based off of the total amount, unless there is a regulatory reason to break it out by transaction.
This means your child just has
--Parent ID (Agreements should be a child to the customer, so should just have the agreement ID)
--Date/Time of transaction
--Type for transaction
--Transaction amount.
--Notes

For your payment schedule, you can work out "Total due divided by number of payment" to tell you how much should be paid each month. Total of all credit less debits would give you the customers balance. Balance divided by your calculated monthly payments tells you how many months they are paid through.

This approach lets you calculate not only the next month payment is due, but the same methodology helps calculate what the amount due is.
As a scenario, say a customer owes $1800 to be paid over 60 months. Each month they need to pay (1800/60) $30. Customer pays $100 up front. Next payment is due month (100/30) month 4 with an amount of $20 due.

If you tried to create all of the records ahead of time, your user would need to go in and try and allocate this on their own. More difficult AND tends to add human errors.