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"
to get this to work , what I did
I created a third table called webaddress using make table query with
only the field "code" from "data main"
I then went to the table and added field "link"
I then created update query using update to
"http:///www.webaddress. com/"& [code] &".html"
that did the merge of data
I then edited the make table query that creates "ddw1" to get the link
field from the webaddress table.
Ideally would like the field to be created when I do the create table
query so it is just one step to do everything.
since the field is not needed in where but table "ddw1" I do not want
to put it in "data main" also some data in code may change.
Any one who know how to do this ?
Thanks 9 3099 se*******@aol.c om wrote in
news:11******** **************@ 74g2000cwt.goog legroups.com:
>
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"
[snipped]
>
Any one who know how to do this ?
Thanks
in the make table query, just past this into a field box 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.co m
I tried it and nothing happened.
Bob Quintal wrote:
se*******@aol.c om wrote in
news:11******** **************@ 74g2000cwt.goog legroups.com:
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"
[snipped]
Any one who know how to do this ?
Thanks
in the make table query, just past this into a field box 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.co m
Ignore my last reply, I did not look closley at what you had typed.
It worked as you put it.
Thanks
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 ?
2) another field contains the exact same piece of 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"
Bob Quintal wrote:
se*******@aol.c om wrote in
news:11******** **************@ 74g2000cwt.goog legroups.com:
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"
[snipped]
Any one who know how to do this ?
Thanks
in the make table query, just past this into a field box 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.co m
se*******@aol.c om wrote in
news:11******** **************@ b28g2000cwb.goo glegroups.com:
Ignore my last reply, I did not look closley at what you had
typed. It worked as you put it.
Thanks
Glad it worked.
>
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 ?
sure!
link: "http://www.wedsite.com/" & StrConv([code], 2) & ".html"
2) another field contains the exact same piece of
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"
If it contains the same information for every field, it does 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:
>se*******@aol. com wrote in news:11******* *************** @74g2000cwt.goo glegroups.com:
>
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"
[snipped]
>
Any one who know how to do this ?
Thanks
in the make table query, just past this into a field box 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.co m
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.co m
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.
2 export
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.
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
the format is (qty 1 pricing)space (number on 2nd level
pricing)space(n umber on 2nd level pricing Times each price for that
level)space (number on 3rd level pricing)space(n umber on 3rd level
pricing Times each price for that level)space(num ber on 4th level
pricing)space(n umber 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?
As always, help is much appreciated.
Bob Quintal wrote:
se*******@aol.c om wrote in
news:11******** **************@ b28g2000cwb.goo glegroups.com:
Ignore my last reply, I did not look closley at what you had
typed. It worked as you put it.
Thanks
Glad it worked.
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 ?
sure!
link: "http://www.wedsite.com/" & StrConv([code], 2) & ".html"
2) another field contains the exact same piece of
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"
If it contains the same information for every field, it does 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:
se*******@aol.c om wrote in
news:11******** **************@ 74g2000cwt.goog legroups.com:
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"
[snipped]
Any one who know how to do this ?
Thanks
in the make table query, just past this into a field box 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.co m
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.co m
se*******@aol.c om wrote in
news:11******** **************@ q16g2000cwq.goo glegroups.com:
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_AfterU pdate() as string
Dim stAllowedChars as string
dim stFrom as string stTo as string
dim iPtr as integer.
stAllowedChars ="ABCDEFGHJKLMN PRTUVWY01234567 89"
'do not allow IOQSXZ, they can be confused with numbers.
stFrom = me.Itemcode.val ue
For iPtr = 1 to len(stFrom)
If instr(1,stAllow edChars,mid(stF rom,iptr,1))>0 then
stTo = stTo & ucase(mid(stFro m,iptr,1))
end if
next iPtr
me.Itemcode.val ue = stTo
end sub
2 export
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.
Just save the query as text file, you do not need to make table
in between.
>
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
No, data in a related child table should be
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(n umber on 2nd level pricing Times each price for
that level)space (number on 3rd level pricing)space(n umber on
3rd level pricing Times each price for that level)space(num ber
on 4th level pricing)space(n umber 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:
>se*******@aol. com wrote in news:11******* *************** @b28g2000cwb.go oglegroups.com:
Ignore my last reply, I did not look closley at what you
had typed. It worked as you put it.
Thanks
Glad it worked.
>
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 ?
sure! link: "http://www.wedsite.com/" & StrConv([code], 2) & ".html"
2) another field contains the exact same piece of
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"
If it contains the same information for every field, it does 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: se*******@aol. com wrote in news:11******* *************** @74g2000cwt.goo glegroups.com:
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"
[snipped]
Any one who know how to do this ?
Thanks
in the make table query, just past this into a field box 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.co m
-- Bob Quintal
PA is y I've altered my email address.
-- Posted via a free Usenet account from http://www.teranews.co m
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.co m
Warning: Do not use Ultimate-Anonymity
They are worthless spammers that are running a scam.
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_AfterU pdate() as string
Dim stAllowedChars as string
dim stFrom as string stTo as string
dim iPtr as integer.
stAllowedChars ="ABCDEFGHJKLMN PRTUVWY01234567 89"
'do not allow IOQSXZ, they can be confused with numbers.
stFrom = me.Itemcode.val ue
For iPtr = 1 to len(stFrom)
If instr(1,stAllow edChars,mid(stF rom,iptr,1))>0 then
stTo = stTo & ucase(mid(stFro m,iptr,1))
end if
next iPtr
me.Itemcode.val ue = stTo
end sub
What if I am not using forms?
and the data needs to be lower case with all characters because they
are part numbers and also will be part of an web address address and
some sites do not support capitals and lower case letters as the same. se*******@aol.c om wrote in
news:11******** **************@ e3g2000cwe.goog legroups.com:
>
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_AfterU pdate() as string Dim stAllowedChars as string dim stFrom as string stTo as string dim iPtr as integer.
stAllowedCha rs ="ABCDEFGHJKLMN PRTUVWY01234567 89" 'do not allow IOQSXZ, they can be confused with numbers. stFrom = me.Itemcode.val ue
For iPtr = 1 to len(stFrom) If instr(1,stAllow edChars,mid(stF rom,iptr,1))>0 then stTo = stTo & ucase(mid(stFro m,iptr,1)) end if next iPtr me.Itemcode.va lue = stTo end sub
What if I am not using forms?
How will you be entering data? If you intend to allow entry of
part numbers in the table, you are doing something dangerous.
If you are planning to import from a file generated outside of
Access, then place the code in a user defined function, and call
that function in a query
and the data needs to be lower case with all characters
because they are part numbers and also will be part of an web
address address and some sites do not support capitals and
lower case letters as the same.
because they are part numbers? The D.O.D. in the usa and NATO
High Command require part numbers in UPPERCASE only, with the
letters excluded as shown in my code.
Some sites? not many, as the web has evolved. If you still
insist, then add one function to the code to convert the string
to lowercase
Change from
me.Itemcode.val ue = stTo
To
me.Itemcode.val ue = StrConv([stTo], 2)
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.co m
Warning: Do not use Ultimate-Anonymity
They are worthless spammers that are running a scam.
not many?
they may be few, but they effect a lot
try these addresses http://news.yahoo.com/s/nm/20060912/...whole_grain_dc http://news.yahoo.com/s/nm/20060912/...whole_grain_dC
they do host business website accounts. It has the same effect.
I think the upper / lower case thing depends upon what OS the hosting
server uses. se*******@aol.c om wrote:
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"
to get this to work , what I did
I created a third table called webaddress using make table query with
only the field "code" from "data main"
I then went to the table and added field "link"
I then created update query using update to
"http:///www.webaddress. com/"& [code] &".html"
that did the merge of data
I then edited the make table query that creates "ddw1" to get the link
field from the webaddress table.
Ideally would like the field to be created when I do the create table
query so it is just one step to do everything.
since the field is not needed in where but table "ddw1" I do not want
to put it in "data main" also some data in code may change.
Any one who know how to do this ?
Thanks
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Dave Smithz |
last post by:
Having adopted someone else's PHP cope and completing a crash course in the
language I came across a (probably common) problem with the current code.
On a registration form, whenever users names have an apostrophe in them it
causes problems as they do not get added to the DB correctly for reasons
that immediately become apparent.
Before...
|
by: Sami |
last post by:
Please bear with me, and if you answer this question, please do it
step by step. I am new at Access, not at all sophisticated. I am
using Office XP. This will need to be read in Access for Office 2000.
I am creating a database to track student athletes. I have created
the following tables. The table title is to the far left, with fields...
|
by: Hasanain F. Esmail |
last post by:
Hi,
Your help will be greatly appriciated.
I have a table with following fields. Table's name is tblTicketNumers
TicketNumberID (It is a key field)
TicketNumber (Text field)
AgentsName (Text field)
I have a form which is NOT BOUND to this table with following controls
|
by: Jim Heavey |
last post by:
Trying to figure out the technique which should be used
to add rows to a datagrid. I am thinking that I would
want an "Add" button on the footer, but I am not quite
sure how to do that. Is that the best method? Do you
have a sample of how to do this?
|
by: John Wildes |
last post by:
Hello all
I'm going to try and be brief with my question, please tell me if I have the
wrong group.
We are querying transaction data from a DB3 database application. The dates
are stored as text fields. Each date for example 10/31/03 or October 31st
2003 is stored as 10/31/A3 in the system. My reasoning for this is because
they...
| |
by: Bryan |
last post by:
hello all...
im trying to add a record to an sql db on ms sql server 2000, using vb.net.
seems to be working.. except for one thing, one of the columns in the
database is a bit datatype, and though i get no syntax errors when
compiling, i get an error indicated that the data would be truncated. the
field is login_status.
ive tried in...
|
by: Justin Fancy |
last post by:
Hi everyone,
I need some help. I'm placing text files into a created database using
vb.Net. The problem is that, i need two seperate sql statements to add
both files because they are in different loops. My output comes out to
be as Follows:
TABLE
----------------------------------------------------
|
by: Bob Jones |
last post by:
I have an odd business requirement and I think that the implementation
is not correct in the terms of OOP development. Any help on the
concepts would be very appreciated!
We currently have a custom Page object which is derived from the base
Page object. We also have custom controls that derive from a base
class that performs custom drawing...
|
by: swethak |
last post by:
Hi,
I am desiging the calendar application for that purpose i used the below code. But it is for only displys calendar. And also i want to add the events to calendar. In that code displys the events when click on that date that perticular event displyed in a text box.But my requirement is to when click on that date that related event...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |