Re: adding a text string to data from one field in one database to another
- From: Bob Quintal <rquintal@xxxxxxxxxxxxx>
- Date: 10 Sep 2006 22:20:45 GMT
sellcraig@xxxxxxx wrote in
news:1157926168.235881.183950@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:
Thanks, your help has allowed me to eliminate a lot of waste
in database.
From 27042kb to 3474 kb Is that a 92% reduction in size?
3 ISSUES
1 validation for code field
If I want the code field to contain only test or numbers - (NO
symbols - ie: /? -;+=space) and also text only in lower case
only. The data in the code field contains product codes of
various lengths I think there are two was I can think of this
working, 1) Not permitting the text to be entered upon
entry. using some validation rule
2) Ignoring the non accepted info and correcting to lower
case when leaving field. Ie: wAsrH 12-4 becomes wasrh124
I looked at
Validation rule suspect- StrConv([code], 2) plus a function
that blocks anything but numbers or letters. ( I do not know
which one) Format do not know
Input mask do not know
I also want to make the field a unique field eliminating any
duplicates of the item code being entered twice.
I strongly suggest UPPERCASE letters, for technical reasons...
I would put this in VB code in the textbox AfterUpdate event.
private sub ItemCode_AfterUpdate() as string
Dim stAllowedChars as string
dim stFrom as string stTo as string
dim iPtr as integer.
stAllowedChars ="ABCDEFGHJKLMNPRTUVWY0123456789"
'do not allow IOQSXZ, they can be confused with numbers.
stFrom = me.Itemcode.value
For iPtr = 1 to len(stFrom)
If instr(1,stAllowedChars,mid(stFrom,iptr,1))>0 then
stTo = stTo & ucase(mid(stFrom,iptr,1))
end if
next iPtr
me.Itemcode.value = stTo
end sub
2 exportJust save the query as text file, you do not need to make table
I Create 2 tables using 2 different queries Table A and Table
B (each one with different data)
After running the create table query, I save Table A as text
file, TAB Delimited with the first line as field names. Once
the text file is created, table A is no longer needed.
After running the other create table query, I save Table B as
text file, COMMA Delimited with the first line as field names.
Once the text file is created, table B is no longer needed.
The purpose of each query is to ultimately create the
corresponding text files.
in between.
No, data in a related child table should be
3 qty discounts
I have items with price breaks a various quantity levels each
of the data is in different fields on the spread sheet
Qty price at that qty price for that qty
1 16 16
20 15.20 304
50 14.78 739
100 14.33 1433
qtys may continue beyond this (or not -depending on item)
Currently I have this data in an Excel spreadsheet created
based upon calculations, sometimes manually changed.
In the database the data needs to be entered into a field (not
in $form) like so: 1 20 304 50 739 100 1433 and another
field containing the single qty pricing 16
Itemcode Qty Price TTLPrice
-------- ---- ----- --------
asd123 1 16 16
asd123 20 15.20 304
asd123 50 14.70 739
vbg007 1 22 22
vbg007 5 21 105
etc.
The reasons for this are many.
the format is (qty 1 pricing)space (number on 2nd level
pricing)space(number on 2nd level pricing Times each price for
that level)space (number on 3rd level pricing)space(number on
3rd level pricing Times each price for that level)space(number
on 4th level pricing)space(number on 4th level pricing Times
each price for that level)...
Can I automate the task of inputting this data from the
spreadsheet to the database?
Any ideas?
see above.
Q
As always, help is much appreciated.
Bob Quintal wrote:
sellcraig@xxxxxxx wrote in
news:1157048491.801970.213020@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:
Ignore my last reply, I did not look closley at what youGlad it worked.
had typed. It worked as you put it.
Thanks
sure!
Two other things
1)
also another issue I came across is that all the data
generated by link: "http://www.wedsite.com/"; & [code] &
".html" need to be lower case
I can run an update query on the "code" field using
StrConv(code, 2) . Can I also do it in the origional make
table query ?
link: "http://www.wedsite.com/"; & StrConv([code], 2) &
".html"
2) another field contains the exact same piece ofIf it contains the same information for every field, it does
information for every record created. I would prefer to not
waste a field in the database when it is only needed for
the table created . the info would be "west2, west3" with
the field name being "Locat" .
Net result - While doing create table query in the new
table also add
a new field that does not exist anywhere in datebase
containing the data "west2, west3"
not belong in the table.
If you need it for a query, do the same thing as for link
local: "west2, west3"
In a form or report, just put it in a label.
Bob Q
Bob Quintal wrote:
sellcraig@xxxxxxx wrote in
news:1156893768.307146.106880@xxxxxxxxxxxxxxxxxxxxxxxxxxx:
[snipped]
Microsoft access
2 tables
table "data main" contains a field called "code"
table "ddw1" is created from a make table query of
"data main"
Goal-
the data in "code" field in needs to be inserted into a
standard web address in the table (the filed name is
link) in ddw1 Example address ---
"http://www.wedsite.com/[code].html";
in the make table query, just past this into a field box
Any one who know how to do this ?
Thanks
in the lower half of the design grid
link: "http://www.wedsite.com/"; & [code] & ".html"
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from
http://www.teranews.com
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com
Warning: Do not use Ultimate-Anonymity
They are worthless spammers that are running a scam.
.
- Follow-Ups:
- References:
- Prev by Date: Re: adding a text string to data from one field in one database to another
- Next by Date: Re: adding a text string to data from one field in one database to another
- Previous by thread: Re: adding a text string to data from one field in one database to another
- Next by thread: Re: adding a text string to data from one field in one database to another
- Index(es):
Relevant Pages
|
