Why am I Getting "Type Miss Match" when Trying to set an Expression Into a Text Field?

I thought that should be & and not + in MS-Access. But then again, I might be wrong!

Hi Nitin,,,

I just change the Expression to:
=[RowNo] & "/" & Count(*) & "/" & [PN-No]
on an Unbound Text and works nicely, as attached.
If I try to bind to a Text Control, then it goes blank
I still don't understand what is going on !?

Issa,

I'm not familiar with the "Sort Text" data type you are referring to above.

First off, I'd advise against storing multiple values in a single column of your data table.  If you really need to store these values, do so in their own fields.  If you need to display data in that format, for some reason, then use the formula in a field in a query.  That column will not be updateable, but should not be a problem otherwise.

re: use of + instead of &
When concatenating values use the & will treat null values as zero length strings, the + will treat them as true NULLs.  When you append a NULL (has no value) to any other value with the +, the result will always be NULL (sort of like infinity + 1 is still infinity).  Example:

Value1 = "3"
Value2 = NULL
Value3 = "4"
Value1 + Value2 + Value3 => NULL
Value1 & Value2 & Value3 => "34"

Dear Dale,,,

I always like your added explanations. It give me the correct understanding.

Since the Expression evaluates correctly in an Unbound control, as shown in the above image, now  I want to be able to save this expression in a field on the table (name = Voucher no).
It is the requirement.
Thanks.

the requirement is probably to get the data -- if a field is calculated, as a general rule, it is best to calculate it when you need it, not store it -- otherwise the data that goes into the equation could change whereas the calculated field that is stored would have to be recalculated again anyway.  If the equation is in the RecordSource as opposed to a Control Source, then you should be able to sort.

If Voucher number will be a set piece of information in the table, then instead of putting an expression in the control source, calculate it in code behind the form -- such as on the form Before Update event

Dear Dale,,,

"I'm not familiar with the "Sort Text" data type you are referring to above".

Sorry for the typo mistake. I meant "Short Text" instead.

Dear Crystal,,,

Can you kindly elaborate on this:
"If Voucher number will be a set piece of information in the table, then instead of putting an expression in the control source, calculate it in code behind the form -- such as on the form Before Update event ".

To Dale & Crystal,,,

Here below is an elaboration on the requirement for you kind advice:
This is the case of a program for preparing and printing Promissory Notes.
The Expression is required for the time of printing and Serializing the "PN-No. "
It is to be like 1 of 9 plus Ref to the Invoice number and year.
Once these PNs are printed, they will stay like this until they are all collected and will not be visited until completely paid.

The Customer has to know which PN he is paying for (or even Partial Payment later). Same as for the Accounting Dept.
The things are working well based on the Form and the SQLs Behind it.
The counter Function is doing a great job there.

The elements of my Expression are available on the Table Except the Line Number (which is my Row No)  and the Total number ( which is the total count of the rows).

If I a can save these 2 fields to the table, then I can make the expression as a Calculated Field on the table itself and the issue will be solved.

Thanks for your help and patience.

Issa,
If you refer back to my previous answer, I told you how to save the concatenated string if you wanted to save it.  If al you want to save is the calculated values, then just save them separately using the FORM's BeforeUpdate event.
Pat

Dear Pat,,,

Lots of thoughts in your above tag.   Thanks for your time.

In fact this is the case of the Function that you and Gustav helped me with last week. With minor modification, it is working nicely. I've drilled many time and still stands. No chances for gaps as a result of deleting or so since I re-query after any change.

Once these records are created and the Promissory Notes are printed, they will never e changed, but they will be needed later for making Payments against them.

Of course, I don't rely on the ID as the PK in such situations.
I am thinking about your idea for a unique identifier key.

Please refer to my elaboration on the requirements up here.

Thanks a lot

Apparently I'm missing something.  Any unbound or calculated data that you want to save in the record, should be populated in the FORM's BeforeUpdate event.  If you want to store the concatenated string, save that.  If you want to save the discrete parts, save those.  In either case, either the single concatenated field or the multi-field parts should have a unique index to prevent your code from generating duplicates.

instead of "+", use " & " to combine the different parts.  With numbers, + acts as addition, not concatenation.

instead of Count(*), could you use dCount?

Dear Pat,,,

I really wish to save the whole string.
Can you tell little more about what should include in the BeforeUpdate event.

Apparently I'm missing something.  Any unbound or calculated data that you want to save in the record, should be populated in the FORM's BeforeUpdate event.  If you want to store the concatenated string, save that.  If you want to save the discrete parts, save those.  In either case, either the single concatenated field or the multi-field parts should have a unique index to prevent your code from generating duplicates.

Sorry if am bothering you a lot on this issue.

It is not a bother.  It just seems like you are asking the same question again which means that my answers didn't help you so I'm trying to figure out what I am overlooking.

I really wish to save the whole string.

I told you exactly how to do that back here. https://www.experts-exchange.com/questions/29016569/Why-am-I-Getting-Type-Miss-Match-when-Trying-to-set-an-Expression-Into-a-Text-Field.html?anchor=a42096754¬ificationFollowed=187641806&anchorAnswerId=42095621#a42095621    Did you use + rather than &?  I also explained the difference.

Dear Patt,,,

I feel shame with this.
I am really lost.
Now Gust Function does not work if a record is deleted.   I thought it will re-sequence the row numbers after the records are saved in the table.

Please don't be distressed.  We all were beginners at one time.

I'm not sure what you are saying isn't working.  It is always best to post code you are having trouble with so we can see it.

Once you save a record, the generated "key" should NEVER be changed so I don't understand the remark about re-sequencing.  Relational databases do not support row numbers.  All data retrieval is via queries (even opening a table in DS view runs a query) and therefore, record order can change.  That makes row numbers meaningless.  Occasionally, we need to save a particular sequence.  You can do that but, you would never reassign the sequence at a later date.  Once a sequence number is assigned to a record and saved, it should be permanent or there is no reason to ever save it at all.

Dear Pat,,,

I just found that if I close and re-open ALL the DB, then open it again, load the Main Form along with its sub-Form, then the Row-No re-sequences correctly.

Does it mean that the deletes are not committed until the DB is all close!?
I have tried this scenario many time. To me this is a strange behavior. I am sure there is nothing else holding the subject file.
All is closed!
I am sure am missing something here.   This could solve the whole issue.
Thanks

Yes Patt,,,

Once a sequence number is assigned to a record and saved, it should be permanent or there is no reason to ever save it at all.
 I agree with this after finalizing the secession and this is the objective.

I am talking about while the Form is Open. Say, added 10 records and then decided the 5th one is not wanted, so deleting it and requery does not help until the whole DB is closed !!

Yes Patt,,,

Once a sequence number is assigned to a record and saved, it should be permanent or there is no reason to ever save it at all.
 I agree with this after finalizing the secession and this is the objective.

I am talking about while the Form is Open. Say, added 10 records and then decided the 5th one is not wanted, so deleting it and requery does not help until the whole DB is closed !!
Opening the DB again and loading the form re-sequences correctly !

Dear Crystal,,

Where and I tried to the sub-form on the  "After Del Confirm", but same.

Dears:

Where,  as I have tried to requery the sub-form on the  "After Del Confirm", but same.
Below is the SQL for the sub-form's Query, if it helps.

SELECT PNDetails.ID, PNDetails.[PN-No], PNDetails.[Pn-No] AS Expr1, [PNDetails]![PN-Date] AS [PN-Date], [PNDetails]![Amount] AS Amount, RowCounter(CStr([ID]),False,CStr([Pn-No])) AS RowNo, PNDetails.Remarks
FROM PNDetails
WHERE (((PNDetails.[Pn-No])=[Forms]![MainPN-MainForm]![PN-No]))
ORDER BY PNDetails.ID, PNDetails.[Pn-No] DESC , PNDetails.[Pn-No] DESC , RowCounter(CStr([ID]),False,CStr([Pn-No]));

Dear Patt,,,

The last situation is :
- While adding lines in the sub form, there is no problem, and the Row Number and the Count work nice.
- If while am on the form, I move up the rows and delete one (or some lines), they get deleted from the S/F. I tried to let the S/F re-query After, On, Before Del   and nothing happens. So the Row-Nos remain as were obtained when were added.
- Closing the F  and the SF and even everything else (i.e. blank screen with nothing opened) so that nothing is supposed to be holding the file.
 
-  WORST OF ALL.
Only if I close the whole DB and re-open it again, and re-open the MF (of course with its SF),   then the net lines  come there with the corrected Row-Nos.
 
- Is this a Symptom of DB Locking?
- I think now my problem can be over if this issue is solved. Either updating within the SF or even after closing the MF/SF  but not with closing and opening the whole DB.

- Do you want me to send you the SQL of the SF query.
 
Thanks for your patience and understanding.

you're welcome ~ happy to help

glad you figured it out ~

Dear Crystal,,,

I know I closed this Thread but I got another new issue now related to saving the Expression Field onto the underlying table.

Shall I open a new question ?

Thanks

it would be better to start a new question

For the benefit of all who may come across this thread, the keyword was to CLOSE the Table before Re-Querying .
I remembered this from other experiences as it is the best way to force the table to commit all pending transactions on it (Add, Del, or Update)

Updates are commited by the time the AfterUpdate event runs for EACH record.  A Requery is required to make the form show added/deleted records.  If you are working with a recordset to which you are making changes in a different process, you would need to close the recordset and reopen ti.

Without belittling the efforts and thoughts of all who contributed on this thread,  I really benefitted from their ideas and suggestions a lot towards the final solution.
Thanks for every one.

you're welcome, Issa