Search table using calculated field. (1 Viewer)

lumiere

New member
Local time
Today, 05:45
Joined
Nov 10, 2019
Messages
29
Hi,

Is there a way to search data using a calculated data field.

On 'EntryForm', name, address, contact, date(Dt) and serial number(Sr) are entered which are combined to produce a calculated result using formula -


Year[Dt] *10000000+Month[Dt]*100000+Day[Dt]*1000+[Sr]

There is a separate primary key with autonumber is present, however the result of this calculated field is supposed to be unique for each entry as serial number for two entries can't be same on a same date.

On 'DataForm', Date and serial number are to be entered by user in combo boxes and text boxes. The
above calculation would be done in background using same formula. This should be used to search table connected with 'EntryForm' and after matching, other data of the same customer, i.e. their name, address and contact number should be displayed in text boxes.

Can someone help me how to do this.
I have tried to use a query but it says that calculated field can't be entered.

Any help would be appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:45
Joined
Oct 29, 2018
Messages
21,357
Hi. I'm not sure I understand your question. What exactly did you want to search for? The whole thing or just parts of it? Can you give us a sample string to search for? Thanks.
 

plog

Banishment Pending
Local time
Today, 07:45
Joined
May 11, 2011
Messages
11,611
You shouldn't have calculated fields in tables. Instead your calculation should be in a query:

Code:
SELECT ID,  Year[Dt] *10000000+Month[Dt]*100000+Day[Dt]*1000+[Sr]  AS CalculatedID
FROM YourTableNameHere

This should be used to search table connected with 'EntryForm' and after matching, other data of the same customer, i.e. their name, address and contact number should be displayed in text boxes.

The two problems with that is 1. You don't tell us how "search" is done. 2. You don't account for multiple matches. Presumably a user could type in just 3 characters and it would match multiple records in your table, then it would be up to them to determine which one they want. So going straight to a screen that shows the record is not really workable.

So, what i would do is build a search form where they type in text, and the form populates with all records that match along with a button to your actual form where they can view the full record. This would be accomplished with form functions (.Filter and DoCmdn.OpenForm) and not queries.
 

lumiere

New member
Local time
Today, 05:45
Joined
Nov 10, 2019
Messages
29
Hi.
I have a table with name, address, contact, zip code, date and serial number. There is also a calculated field in same table using date and serial.

I want another form to be used by customer to enter date and serial only. The calculation are done in background and all matching record from the table be obtained and shown in seperate text boxes on this new form (such as name, address, contact etc)
 

lumiere

New member
Local time
Today, 05:45
Joined
Nov 10, 2019
Messages
29
Hi. I'm not sure I understand your question. What exactly did you want to search for? The whole thing or just parts of it? Can you give us a sample string to search for? Thanks.

For example for 22nd September, 2020 an entry is made for 15th customer. Their name, address etc are stored. In calculated column, the number stored is 20200922015.

On another search form, another user enters date as 22nd September 2020 and serial number as 15. The form should display name and other details of the above customer.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:45
Joined
Oct 29, 2018
Messages
21,357
For example for 22nd September, 2020 an entry is made for 15th customer. Their name, address etc are stored. In calculated column, the number stored is 20200922015.

On another search form, another user enters date as 22nd September 2020 and serial number as 15. The form should display name and other details of the above customer.
Hi. Do you have two separate fields in the table to search for the date and the serial number, or are you stuck with using the calculated field?
 

lumiere

New member
Local time
Today, 05:45
Joined
Nov 10, 2019
Messages
29
You shouldn't have calculated fields in tables. Instead your calculation should be in a query:

Code:
SELECT ID,  Year[Dt] *10000000+Month[Dt]*100000+Day[Dt]*1000+[Sr]  AS CalculatedID
FROM YourTableNameHere



The two problems with that is 1. You don't tell us how "search" is done. 2. You don't account for multiple matches. Presumably a user could type in just 3 characters and it would match multiple records in your table, then it would be up to them to determine which one they want. So going straight to a screen that shows the record is not really workable.

So, what i would do is build a search form where they type in text, and the form populates with all records that match along with a button to your actual form where they can view the full record. This would be accomplished with form functions (.Filter and DoCmdn.OpenForm) and not queries.

Thanks for reply.
I only intend to use calculated field in table is to add a sort of unique identification to each record. All the other fields contain data which may be repeated. As I intend to enter every customer on a unique serial number everyday (Starting from 1), i combined date and serial number, so there is only one record per serial number on any day.
 

lumiere

New member
Local time
Today, 05:45
Joined
Nov 10, 2019
Messages
29
Hi. Do you have two separate fields in the table to search for the date and the serial number, or are you stuck with using the calculated field?

In my main table I have separate field containing Date, serial number and the calculated field (Double data type containing the result)
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:45
Joined
Sep 21, 2011
Messages
14,038
You are not using any brackets for any of those functions?
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:45
Joined
Sep 21, 2011
Messages
14,038
Thanks for reply.
I only intend to use calculated field in table is to add a sort of unique identification to each record. All the other fields contain data which may be repeated. As I intend to enter every customer on a unique serial number everyday (Starting from 1), i combined date and serial number, so there is only one record per serial number on any day.
So effectively it is the dt in yyyymmdd format with sr possibly concatenated
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:45
Joined
Oct 29, 2018
Messages
21,357
In my main table I have separate field containing Date, serial number and the calculated field (Double data type containing the result)
In that case, it might be easier to just search those two fields separately rather than try to figure out how to search the calculated field. You might give it a try and let us know if you can't figure it out.
 

lumiere

New member
Local time
Today, 05:45
Joined
Nov 10, 2019
Messages
29
In that case, it might be easier to just search those two fields separately rather than try to figure out how to search the calculated field. You might give it a try and let us know if you can't figure it out.


Ok i'll try to search the two fields separately.
 

strive4peace

AWF VIP
Local time
Today, 07:45
Joined
Apr 3, 2020
Messages
1,003
@lumiere, make a combo box where the first field is the primary key and the second column is your calculated field.
Set these properties:
Column count=2
ColumnWidths = 0;6 'I'm not sure how much space you need for your column -- adjust if desired
ListWidth = 6.5 'add the column widths and then +0.5 allowance for scrollbar

Then, when the user picks what they want, you can run this code to find the record:
VBA > Form > Find Record
http://msaccessgurus.com/VBA/Code/Form_FindRecordN.htm
 

Cronk

Registered User.
Local time
Today, 23:45
Joined
Jul 4, 2013
Messages
2,770
I'm having trouble envisaging why you would need to know who was the fifteenth customer on a particular day. Or is this a way to generate a unique customer key? Just about everybody would use an autonumber as key index. If you do need to know the order of customers, record date and time in a date field.
 

lumiere

New member
Local time
Today, 05:45
Joined
Nov 10, 2019
Messages
29
Good luck! Please let us know how it goes.

Thanks.
I tried to make a query with name, address, contact, date and serial number. Criteria was set for date and serial number. Date and serial number were entered through a text box on a form, using '[Forms]! [DataForm]! [Dt]' and '[Forms]! [DataForm]! [Sr]' in date and serial number column, respectively, in query.

The resulting query contained only one entry with all the details.
I got rid of calculated field.
Thank you.

However, is there a way or vba code to fill the details like name, address, contact number in individual text boxes like auto fill.?
 

lumiere

New member
Local time
Today, 05:45
Joined
Nov 10, 2019
Messages
29
I'm having trouble envisaging why you would need to know who was the fifteenth customer on a particular day. Or is this a way to generate a unique customer key? Just about everybody would use an autonumber as key index. If you do need to know the order of customers, record date and time in a date field.
The fifteenth customer was an example. The main purpose was to generate a unique customer identification number. All other field contain data which may be repeatable so i tried to make a unique field which could be easily calculated by summing up date and serial number

I had used a similar database before made on visual foxpro before.
 

Users who are viewing this thread

Top Bottom