Issa S.
asked on
Why am I Getting "Type Miss Match" when Trying to set an Expression Into a Text Field?
Hi All,,,
Why am I Getting "Type Miss Match" when Trying to set an Expression Into a Text Field?
I Have a text field set to be in a Bound Control on the Form.
When I try to use Set Value like this:
Voucher = ([RowNo]+"/"+Count(*)+"/"+ [PN-No])
I get type Miss Match error. Knowing that Voucher is defined as "Sort Text" on the table.
Thanks
Why am I Getting "Type Miss Match" when Trying to set an Expression Into a Text Field?
I Have a text field set to be in a Bound Control on the Form.
When I try to use Set Value like this:
Voucher = ([RowNo]+"/"+Count(*)+"/"+
I get type Miss Match error. Knowing that Voucher is defined as "Sort Text" on the table.
Thanks
I thought that should be & and not + in MS-Access. But then again, I might be wrong!
ASKER
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"
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"
ASKER
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.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
"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
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
ASKER
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
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?
instead of Count(*), could you use dCount?
ASKER
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.
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.
ASKER
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.
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.
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.
ASKER
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
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
ASKER
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 !!
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 !!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 !
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 !
ASKER
Dear Crystal,,
Where and I tried to the sub-form on the "After Del Confirm", but same.
Where and I tried to the sub-form on the "After Del Confirm", but same.
ASKER
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]),Fals e,CStr([Pn -No])) AS RowNo, PNDetails.Remarks
FROM PNDetails
WHERE (((PNDetails.[Pn-No])=[For ms]![MainP N-MainForm ]![PN-No]) )
ORDER BY PNDetails.ID, PNDetails.[Pn-No] DESC , PNDetails.[Pn-No] DESC , RowCounter(CStr([ID]),Fals e,CStr([Pn -No]));
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]),Fals
FROM PNDetails
WHERE (((PNDetails.[Pn-No])=[For
ORDER BY PNDetails.ID, PNDetails.[Pn-No] DESC , PNDetails.[Pn-No] DESC , RowCounter(CStr([ID]),Fals
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you're welcome ~ happy to help
glad you figured it out ~
glad you figured it out ~
ASKER
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
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 .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.
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)
ASKER
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.
Thanks for every one.
you're welcome, Issa