Data Types

[Reez]

Hi,
Can someone please help me do the following:
I want a text field to be input in this way: number,number in the same text field.
I also would like that if the user hasn't entered any information yet, the table would show (startvalue,end value) in the text field and when the mouse cursor is pressed in that field the default writing would vanish allowing the user to enter ( but the comma would remain there to split the text field).
Thank you

 

[MrQ]

Reez,
Hi there. You can't have value, value in a single field in a table. How is your user going to input data? Via a form? What is the purpose of the format you're after?
Shout back. I'm sure we can figure it out.

Q

 

[Reez]

Oh trust, I'm shouting alright!
You see, the column is "frequency band (MHz)" So normally, i want the start frequency and the end frequency separated by a comma, I don't want to have 2 columns one for the start frequency and another for the end frequency. But I think it is possible since we can have a date format like this (dd,mm,yyyy) in the same field right? oh and speaking of that date format, how can i do that too?

thanx alot MrQ,

 

[MrQ]

Reez,
I think you maybe thinking of the table like a spreadsheet. Remember in the table it makes much more sense to have your start and end as two seperate fields in you record which you can then query and display as you choose via a form.

 

[MrQ]

Hi Reez,
I've posted a sample here to try to illustrate what I mean.

Q

 

[Reez]

Ok, so assume I create 2 columns one for start freq and another for end freq. So how can I create a form to display only 2 columns : antenna ID and Frequency band (merging the values from the start and end columns separated by a column)
I see what you did, but I don't know HOW TO do it, I mean in the form specs, how do i do that?

Thanx

 

[MrQ]

Let's say you have a table with four columns. On your form you only want to display two. You can set the recordsource of the for as the table but the more normal route is to use a query. If you create a select query against your table for all fields then create a form with the form design wizard, using your query as the recordsource of the form. You now have the option to include whatever fields from your query on your form. If you want to display two values in one field, the easiest way is to have the two values as fields on your form, both set to 'not visible'. You can now create a further field in the way the field is set in my sample. That is =yourfirstfield & " , " & yoursecondfield
Q

 

[Reez]

Thanks, but you see, the form is an output, it's just a readonly for displaying the queries. That's not what I want, isn't there a way I can make my own data type. What do ""attachment" and "OLE object mean in the data type combo box in access?

 

[MrQ]

Can you explain to me what your final goal is. Think I need to work backwards from waht you want to end up with.

Q

 

[Reez]

It's very simple, ihave a table and a column for the frequency band that I want to format in the design view. So, in the design view, I can chose what data type I want to enter (text, number..) I just want a data type format which is not there, but it's really close to the date format considering there are separators. I just want to enter the values like this (number,number).

 

[MrQ]

You can, in a tbale create your own customerformat for a field. Open you table in design view and select the field you want to edit. In the lower portion of the screen you will see the input mask option. Select this then click to edit the list. Now you can name your Description. In the input mask, I think you need >0000,0000, an underscore in the place holder, anyt example in teh sample data eg 1234,4321 and the mask type as is.
Q

 

[Reez]

OH GOD, it worked, thank you thank you MrQ,

 

[Reez]

Oh, but what if I don't want the digits to have a fixed lenght. When I try it, it only allows me to enter exactly 4 digits. How can I change that, I want the suer to enetr any number of digit.

 

[MrQ]

How about leading zeroes. User entering 00011,00999 to pad the field.
Q

 

[Reez]

Yeah, I just tried this one, but with 9999,9999 instead of 0s. It's working this way, I just wish these things could be a bit more flexible.
I was wondering, if we were using GUI, will the text field also appear as the input mask output? Like "0"'s instead of blank positions?

 

[MrQ]

The field would appear as you've formatted it. However you could change the formatting on your form.

Q

 

[The_Doc_Man]

Here is the problem you are asking for.

If EVER at ANY TIME IN YOUR LIFE you need to separate the two things on either side of the comma, you will wish at that time that you HAD split them into two fields, either of which would be numeric and very well-behaved.

If at any time you need to know separately what the start and end values were, then NEVER EVER IN A GAZILLION YEARS should you combine fields this way. It is wrong because if you DO need to separate the fields, then your design ignored your function. And that is one of the very few near-absolutes of any database design project. Design follows function, not the other way around.

 

[MrQ]

Totally agree with you Doc_Man. Found it hard to try and put my point across. Grateful for your input.

Q

 

[Banana]

Furthermore, I should note that storing more than one value in one column violate normalization, and will make your queries unnecessarily complex. How would you search only part of field? Doable but PITA compared to having two columns dedicated to each atomic data.

Also, as a general rule of thumb, we don't stored calculated store. If you want to show band or something calculated from start/end, it's always cheaper to just store the components and calculate the result on the fly than it is to store the final result and have no idea how we arrived at the final result.

Finally, it's practically always easier to format the components into a complex data than it is to format a complex data back into its components. If you do not want to display start/end for a report, you can just format using that information and display the formatted only.

HTH.