making virtual fields along with real fields in select statement

[shripaldalal]

hi all,

i have the following fields....

Document_Name, Document_No, Document_Date, Order_No, Order_Date,
Peripheral

The first 5 you can understand, peripheral is a memo field which
contains all less important (but important!) details like Address1,
Address2, Place_Of_Delivery in a comma delimited string such as:

Address1:Princess Street;Address2:Mumbai;Place_Of_Delivery:Bhiwandi;

I have already done something like this

select document_no, document_date, order_no, order_date,
getperipheral(Place_Of_Delivery) as Delivery_Place from mastertable

then getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
'Place_Of_Delivery') - 17)

so i sql looks like

select document_no, document_date, order_no, order_date,
getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
'Place_Of_Delivery') - 17) as Delivery_Place from mastertable

This method also works fortunately but there is only one huge
problem....

if place of delivery is not there at all.... and peripheral field is
blank.....then the ENTIRE RECORD DOES NOT SHOW UP OR THERE IS AN
ERROR.......is there any inbuilt function in access that can do
this.....this is a night mare.......

thanks,
shripal dalal.

[salad]

shripaldalal wrote:

hi all,

i have the following fields....

Document_Name, Document_No, Document_Date, Order_No, Order_Date,
Peripheral

The first 5 you can understand, peripheral is a memo field which
contains all less important (but important!) details like Address1,
Address2, Place_Of_Delivery in a comma delimited string such as:

Address1:Princess Street;Address2:Mumbai;Place_Of_Delivery:Bhiwandi;

I have already done something like this

select document_no, document_date, order_no, order_date,
getperipheral(Place_Of_Delivery) as Delivery_Place from mastertable

then getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
'Place_Of_Delivery') - 17)

so i sql looks like

select document_no, document_date, order_no, order_date,
getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
'Place_Of_Delivery') - 17) as Delivery_Place from mastertable

This method also works fortunately but there is only one huge
problem....

if place of delivery is not there at all.... and peripheral field is
blank.....then the ENTIRE RECORD DOES NOT SHOW UP OR THERE IS AN
ERROR.......is there any inbuilt function in access that can do
this.....this is a night mare.......

thanks,
shripal dalal.

On something like this I'd create a function. Something similar to the
following function. Insert this in a module and test.

Public Function GetAddressData(strAddr, strType) As String
Dim intPos As Integer
Dim strData As String

If Not Isnull(strAddr) then
'see if that section of address exists. If so, get
'the beginning of the end of the word
intPos = InStr(strAddr, strType)

'get start of the field data if found
If intPos > 0 Then strData = Mid(strAddr, _
intPos + Len(strType) + 1)

'if strData is empty, then no data for that field
If strData > "" Then
'look for field terminating ";" character
intPos = InStr(strData, ";")

If intPos > 0 Then
GetAddressData = Left(strData, intPos - 1)
Else
'field was not terminated at end with a ;
GetAddressData = strData
End If
End If
endif
End Function

Now you can create columns in the query using the following. If I
understood correctly, Peripheral is the memo field name
Address1:GetAddressData([Peripheral],"Address1")
Address2:GetAddressData([Peripheral],"Address2")
Place_Of_Delivery:GetAddressData([Peripheral],"Place_Of_Delivery")

This parses out the 3 fields.

[shripaldalal]

Hi,

I am using ASP as my front end.... Where can i put this function ?
within access or asp ? if i put it in asp then will it run in the sql ?
or where can i put it in access that it will run when i call an sql
from asp... ? something like this:

select document_name, address1:getaddressdata([peripheral], "address1")
from mytable

regards,
shripal.

salad wrote:

shripaldalal wrote:

hi all,

i have the following fields....

Document_Name, Document_No, Document_Date, Order_No, Order_Date,
Peripheral

The first 5 you can understand, peripheral is a memo field which
contains all less important (but important!) details like Address1,
Address2, Place_Of_Delivery in a comma delimited string such as:

Address1:Princess Street;Address2:Mumbai;Place_Of_Delivery:Bhiwandi;

I have already done something like this

select document_no, document_date, order_no, order_date,
getperipheral(Place_Of_Delivery) as Delivery_Place from mastertable

then getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
'Place_Of_Delivery') - 17)

so i sql looks like

select document_no, document_date, order_no, order_date,
getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
'Place_Of_Delivery') - 17) as Delivery_Place from mastertable

This method also works fortunately but there is only one huge
problem....

if place of delivery is not there at all.... and peripheral field is
blank.....then the ENTIRE RECORD DOES NOT SHOW UP OR THERE IS AN
ERROR.......is there any inbuilt function in access that can do
this.....this is a night mare.......

thanks,
shripal dalal.

On something like this I'd create a function. Something similar to the
following function. Insert this in a module and test.

Public Function GetAddressData(strAddr, strType) As String
Dim intPos As Integer
Dim strData As String

If Not Isnull(strAddr) then
'see if that section of address exists. If so, get
'the beginning of the end of the word
intPos = InStr(strAddr, strType)

'get start of the field data if found
If intPos > 0 Then strData = Mid(strAddr, _
intPos + Len(strType) + 1)

'if strData is empty, then no data for that field
If strData > "" Then
'look for field terminating ";" character
intPos = InStr(strData, ";")

If intPos > 0 Then
GetAddressData = Left(strData, intPos - 1)
Else
'field was not terminated at end with a ;
GetAddressData = strData
End If
End If
endif
End Function

Now you can create columns in the query using the following. If I
understood correctly, Peripheral is the memo field name
Address1:GetAddressData([Peripheral],"Address1")
Address2:GetAddressData([Peripheral],"Address2")
Place_Of_Delivery:GetAddressData([Peripheral],"Place_Of_Delivery")

This parses out the 3 fields.

[salad]

shripaldalal wrote:

Hi,

I am using ASP as my front end.... Where can i put this function ?
within access or asp ? if i put it in asp then will it run in the sql ?
or where can i put it in access that it will run when i call an sql
from asp... ? something like this:

select document_name, address1:getaddressdata([peripheral], "address1")
from mytable

regards,
shripal.
You might want to post your question again...with my code. The reason I
say so is 1) I'm not that familiar with ASP and 2) very, very few people
look back at reply's over a day old unless they may be following the
thread. The reason I bothered to check is that I spent more than 2
minutes writing some code that works and wanted to see if you'd reply.

My take on it? If the ASP is using an Access database as the back end,
and it is calling the Access query (with maybe the function call in a
module in the back end), it would work within Access. If it doesn't and
ASP runs VBScript, I suppose you could return the memo field and keep
the control for that hidden and then make the control sources parse the
field. For example, in an Access form I can make the control source
=getaddressdata([peripheral], "address1")
and it will return the info from the function call to getaddressdata.

Many of the heavyweights here know ASP, or have a general concept of it.
I again suggest a repost...along with the function...or else a
reference that getaddressdata([peripheral], "address1") is an Access
function call that extracts the data in a memo field for address1. If
you don't get a good response then post your question to an ASP
newsgroup that has familiarity with vbscript or programming.


salad wrote:

shripaldalal wrote:

hi all,

i have the following fields....

Document_Name, Document_No, Document_Date, Order_No, Order_Date,
Peripheral

The first 5 you can understand, peripheral is a memo field which
contains all less important (but important!) details like Address1,
Address2, Place_Of_Delivery in a comma delimited string such as:

Address1:Princess Street;Address2:Mumbai;Place_Of_Delivery:Bhiwandi;

I have already done something like this

select document_no, document_date, order_no, order_date,
getperipheral(Place_Of_Delivery) as Delivery_Place from mastertable

then getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
'Place_Of_Delivery') - 17)

so i sql looks like

select document_no, document_date, order_no, order_date,
getperipheral(Place_Of_Delivery) i replace with mid(peripheral,
instr(peripheral, 'Place_Of_Delivery') + 17, instr(instr(peripheral,
'Place_Of_Delivery'), peripheral, ';') - instr(peripheral,
'Place_Of_Delivery') - 17) as Delivery_Place from mastertable

This method also works fortunately but there is only one huge
problem....

if place of delivery is not there at all.... and peripheral field is
blank.....then the ENTIRE RECORD DOES NOT SHOW UP OR THERE IS AN
ERROR.......is there any inbuilt function in access that can do
this.....this is a night mare.......

thanks,
shripal dalal.

On something like this I'd create a function. Something similar to the
following function. Insert this in a module and test.

Public Function GetAddressData(strAddr, strType) As String
Dim intPos As Integer
Dim strData As String

If Not Isnull(strAddr) then
'see if that section of address exists. If so, get
'the beginning of the end of the word
intPos = InStr(strAddr, strType)

'get start of the field data if found
If intPos > 0 Then strData = Mid(strAddr, _
intPos + Len(strType) + 1)

'if strData is empty, then no data for that field
If strData > "" Then
'look for field terminating ";" character
intPos = InStr(strData, ";")

If intPos > 0 Then
GetAddressData = Left(strData, intPos - 1)
Else
'field was not terminated at end with a ;
GetAddressData = strData
End If
End If
endif
End Function

Now you can create columns in the query using the following. If I
understood correctly, Peripheral is the memo field name
Address1:GetAddressData([Peripheral],"Address1")
Address2:GetAddressData([Peripheral],"Address2")
Place_Of_Delivery:GetAddressData([Peripheral],"Place_Of_Delivery")

This parses out the 3 fields.