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

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

 

[Pat Hartman]

Would this be the proper application of the concept you have in mind?

Basically, the user is always right (sometimes). If your user really wants separate columns for debits/credits in reports, that is innocuous and should not impact the design so you can just say yes. If he wants separate columns for data entry, that does impact the design and all calculations forever so I would try to talk him off this ledge. Working with a single column when handling calculations and validation is simpler.

I don't like generating "future" records but for some applications, that may work better. You will need to decide but you need to think out the entire processing logic in detail before committing to the table design. Write it down. This takes discipline. Do not skip this step because changing the database after the fact is a lot of work and very prone to errors.

If the future transactions can never change, then I suppose adding them will do no harm. However, if the contract can be cancelled, what happens to all the future invoices or appointments, etc sitting out there? Will you leave them and mark them inactive? If you do that, then every query you ever write has to deal with this dead data. Will you delete them? In an audit, how will you explain the missing records? Usually, I don't generate them when they are "invoices" but to support scheduling, you have to generate them when they are "appointments". Also, it is probably OK to delete future appointments without making an audit trail but "invoices" involve money and that requires much more secure control.

So, if you want more help with "thinking", we'll stay with you. I would need to have a better understanding of the application to provide suggestions on how to handle future items.

 

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

 

[Pat Hartman]

Keeping an existing layout has some merit in that the user is comfortable with it, BUT, not when it violates normal forms. The spreadsheet is laid out with 60 columns and totals at the end. In a relational database, each payment should be a separate row. That means that you are not welded to 60 payments. You can have 36 or 72 if that will help sales. Once the data is normalized, the number of payments is irrelevant. Generally, the user would want to know:
1. Is the account current?
2. How delinquent is it if it is in arrears?
3. Have fees been assessed?
4. Have fees been collected?
5. Summaries of this over the life of the account.
6. When is the next payment due?
7. Reports of all accounts that are delinquent.
8. If this app is also used for debt collection, then you need another section to manage collection attempts with comments.

For this type of Application, I would not generate all payments ahead of time. I would only generate the next payment. So depending on whether your payments are all due on the same day of if they are due on different days, I would run a query either monthly or weekly or daily that generates the next invoice for each account. In the query, any late fee for the previous period would be calculated and included as part of the invoice.