MS Access Continuous Form one columns field is not displaying

[strager]

I have several search forms. Each one is the same style, with similar queries. In fact, I copy one to make another.
For some reason, ???, only one column In one of my search forms not displaying data and looks like the field is not visible. All of the other columns displays the data correctly. There is no code behind that would set the field to visible but yet it doesn't display. I've triple checked everything I could think of but have not been able to get it to display. I must be missing something, but I can't figure it out.

Query
SELECT tblBankAccounts.BankID, tblBankTransactions.BankTransID, tblBankTypes.BankType, tblBankAccounts.BankName, tblBankTransactionTypes.TransType, tblBankAccounts.RoutingNumb, tblBankAccounts.AccountNumb, tblBankTransactions.Amt, tblBankTransactions.DateOfTrans, tblBankTransactions.Balance, tblPaymentMethods.PaymentMethod, tblBillsPaidDetails.CheckNumber, [BankName] & [BankType] & [DateOfTrans] & [TransType] & [PaymentMethod] & [CheckNumber] & [Amt] AS SearchTerm
FROM tblPaymentMethods RIGHT JOIN (tblBankTypes INNER JOIN (tblBankAccounts INNER JOIN (tblBankTransactionTypes INNER JOIN ((tblBankTransactions LEFT JOIN tblBillsPaidDetails ON tblBankTransactions.PaidDetailID = tblBillsPaidDetails.PaidDetailID) LEFT JOIN tblBankDepositDetails ON tblBankTransactions.DepositDetailID = tblBankDepositDetails.DepositDetailID) ON tblBankTransactionTypes.BankTransTypeID = tblBankTransactions.BankTransTypeID) ON tblBankAccounts.BankID = tblBankTransactions.BankID) ON tblBankTypes.BankTypeID = tblBankAccounts.BankTypeID) ON tblPaymentMethods.PaymentMethodID = tblBillsPaidDetails.PaymentMethodID
WHERE ((([BankName] & [BankType] & [DateOfTrans] & [TransType] & [PaymentMethod] & [CheckNumber] & [Amt]) Like "*" & [Forms]![frmCustomNav]![fSubForm].[Form]![txtDummy] & "*"))
ORDER BY tblBankTypes.BankType, tblBankTransactions.DateOfTrans DESC;

 

[GaP42]

Notice that your screen dump - on the far RHS shows a partially hidden column - that appears to show Amount. Could it be that the second amount column is a duplicate of the first and is not represented in the underlying query?
What is the underlying query of the form?
The properties of the txtamt column do not appear to correspond to the Amount column in design view.

 

[theDBguy]

Can you post a sample db with test data?

 

[strager]

Notice that your screen dump - on the far RHS shows a partially hidden column - that appears to show Amount. Could it be that the second amount column is a duplicate of the first and is not represented in the underlying query?
What is the underlying query of the form?
The properties of the txtamt column do not appear to correspond to the Amount column in design view.

There is only one Amt field in the query and the field name is Amt. txtAmt is the name of the form field but it is bound to the Amt field in the query. I always rename form fields so there isn't a conflict in names.
My pics may not be clear. I've marked the fields and will add them to my post. I've also added my sql. Thanks

 

[strager]

Can you post a sample db with test data?

Unfortunately it would be very difficult to create a sample db. I did post my sql and added more pictures

 

[Gasman]

Unfortunately it would be very difficult to create a sample db. I did post my sql and added more pictures

Really?
Create a new DB. Import just what is needed to see the problem. According to you one form and one query? Would need the data for the query as well of course.

Hardly difiicult.

 

[MajP]

Two possibilities.
1. The value for amt is null or ""
2. There are values in there and we cannot see them due to formatting, font size etc.

To verify put any event on the form. Button would work or double click something
msgbox "Amount: " & me.txtamt

If a value is returned for any record then we know it is 2 and not 1.
If 2 then delete the txtbox and add new one. Since you are copying and pasting new forms then could have copied over something from another form not needed on this.

 

[MajP]

Here is a readable version of the query:

SELECT tblbankaccounts.bankid,
       tblbanktransactions.banktransid,
       tblbanktypes.banktype,
       tblbankaccounts.bankname,
       tblbanktransactiontypes.transtype,
       tblbankaccounts.routingnumb,
       tblbankaccounts.accountnumb,
       tblbanktransactions.amt,
       tblbanktransactions.dateoftrans,
       tblbanktransactions.balance,
       tblpaymentmethods.paymentmethod,
       tblbillspaiddetails.checknumber,
       [bankname] & [banktype] & [dateoftrans] & [transtype] & [paymentmethod] &
       [checknumber] & [amt] AS SearchTerm
FROM   tblpaymentmethods
       RIGHT JOIN (tblbanktypes
                   INNER JOIN (tblbankaccounts
                               INNER JOIN (tblbanktransactiontypes
                                           INNER JOIN ((tblbanktransactions
                                           LEFT JOIN tblbillspaiddetails
                                                  ON
       tblbanktransactions.paiddetailid
       =
                  tblbillspaiddetails.paiddetailid)
                  LEFT JOIN tblbankdepositdetails
                  ON
                  tblbanktransactions.depositdetailid =
                  tblbankdepositdetails.depositdetailid)
                  ON tblbanktransactiontypes.banktranstypeid =
                  tblbanktransactions.banktranstypeid)
                  ON tblbankaccounts.bankid = tblbanktransactions.bankid)
                  ON tblbanktypes.banktypeid = tblbankaccounts.banktypeid)
               ON tblpaymentmethods.paymentmethodid =
                  tblbillspaiddetails.paymentmethodid
WHERE  (( ( [bankname] & [banktype] & [dateoftrans] & [transtype] &
            [paymentmethod] &
                        [checknumber] & [amt] ) LIKE
        "*" & [forms] ! [frmcustomnav] ! [fsubform].[Form] ! [txtdummy] & "*"
               ))
ORDER  BY tblbanktypes.banktype,
          tblbanktransactions.dateoftrans DESC;

Does this search work OK for the user? Seems kind of random and limited, and I would think null propagation would cause issues.

I would think if I wanted to search for an amount 123 I do not want to return accounts with 123 or check numbers with 123

 [bankname] & [banktype] & [dateoftrans] & [transtype] &
            [paymentmethod] &
                        [checknumber] & [amt] ) LIKE
        "*" & [forms] ! [frmcustomnav] ! [fsubform].[Form] ! [txtdummy] & "*"

Unfortunately it would be very difficult to create a sample db.

Scramble Text

This function replaces every character with a random similar character. I use this function to anonymize data in tables. Almost but not quite entirely unlike the post made by Micron a few months ago. Share & Enjoy! Option Compare Binary Option Explicit Public Function ScrambleIt(strString...

www.access-programmers.co.uk

 

[strager]

Well I figured it out and it was toooo simple. I had anchored the header Amount to the right so every time I would run it the labels moved over.
Thanks everyone for responding. I do truly appreciate it.

 

[plog]

That's some very unkosher SQL.

1. You shouldn't mix LEFT and RIGHT JOINs. I'm sure a computer can discern it, but my brain can't parse it. 1A--I'm in the camp a RIGHT JOIN should never be used. I like the 'main' datasource of my query to be in the FROM. When you use a RIGHT JOIN that datasource essentially becomes the 'main' datasource and all others are compared to it and the FROM datasource is essentially deprecated in the query it is based on. 1B--it would be one thing if you just used RIGHT JOINs and flipped the FROM datasource like I illustrated above, but when you throw in a LEFT JOIN you now have a second flow of tables to keep track of in your priorities. It just gets super confusing to keep track of which datasources are superior (show all from table A, only matching from table B) when you have LEFT and RIGHT Joins.

2. Criteria on a LEFT JOINed datasource. When you LEFT JOIN a datasource and then include one of its fields in the WHERE you have logically undone the LEFT JOIN and created an INNER JOIN. A LEFT JOIN table will populate NULL values for its fields if no matching record is found in main datasource (show all from A, only matching from B). So even if there is no match, it will show a record but leave the LEFT JOIN fields blank. But if you implement criteria on any of those fields, no record at all will be displayed in your query. Absent records will never allow the main table to show a record in your query because NULL will never meet the criteria (unless you specifically allow NULLs through, which you didn't.)

I would really check your data to make sure that query is doing what you want. Because of the things I addressed above its very likely that it's not showing you all the records you want to allow in it.

 

[strager]

That's some very unkosher SQL.

1. You shouldn't mix LEFT and RIGHT JOINs. I'm sure a computer can discern it, but my brain can't parse it. 1A--I'm in the camp a RIGHT JOIN should never be used. I like the 'main' datasource of my query to be in the FROM. When you use a RIGHT JOIN that datasource essentially becomes the 'main' datasource and all others are compared to it and the FROM datasource is essentially deprecated in the query it is based on. 1B--it would be one thing if you just used RIGHT JOINs and flipped the FROM datasource like I illustrated above, but when you throw in a LEFT JOIN you now have a second flow of tables to keep track of in your priorities. It just gets super confusing to keep track of which datasources are superior (show all from table A, only matching from table B) when you have LEFT and RIGHT Joins.

2. Criteria on a LEFT JOINed datasource. When you LEFT JOIN a datasource and then include one of its fields in the WHERE you have logically undone the LEFT JOIN and created an INNER JOIN. A LEFT JOIN table only gets its fields shown if it matches a record that is in the FROM (show all from A, only matching from B). A LEFT JOIN will allow fields that come from that datasource to be NULL, but if you implement criteria on it, no NULL values will be able to pass through because a NULL will never meet the criteria (unless you specifically allow NULLs through, which you didn't.

I would really check your data to make sure that query is doing what you want. Because of the things I addressed above its very likely that it's not showing you all the records you want to allow in it.

Many years ago when I used to teach relational database design, I really didn't care for it either. but under certain circumstances it works.