473,378 Members | 1,140 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Which query is running

I have a number of queries which use code for the output of 1 or more
fields. For example
Address:GetAddress(AddressID, True, 60)
Address ID Points to an Address in a table - Address Line1, Line 2, Line 3,
Town, County, Post Code, Country.
The True is whether to include the country and the 60 is the maximum line
length (after which a line feed is inserted). All this is to output to a
formatted RTF file for a handbook.

In addition, I have a table that can also hold parameters so for example
if I am running Query 1, I want Address:GetAddress(AddressID, True, 60)
if I am running Query 2, I want Address:GetAddress(AddressID, False, 40)

The essentials of this table are QueryName ( The query or subquery that is
calling the function) i.e. Query1 or Query2
Field Name (The field that
calls the function) i.e. Address
NewParameters
i.e. -1,60 or 0,40

The Function GetAddress first calls a routine to get up to 4 new
parameters.

Function GetParameters(QueryName As String, FieldName As String, Optional
P1, Optional P2, Optional P3, Optional P4)

Dim MyDb As Database
Dim ParamSet As Recordset
Dim QDF As QueryDef
Dim i As Integer, j As Integer

On Error GoTo GetParameters_Err

Set MyDb = CurrentDb
Set ParamSet = MyDb.OpenRecordset("QQueryParameters")
With ParamSet
Do Until .EOF
If !QueryName = QueryName And !FieldName = FieldName Then ' Right
record
GoTo ExtractParams
End If
.MoveNext
Loop

GoTo GetParameters_Exit

ExtractParams:
If !FieldParams = "" Then ' No parameter
GoTo GetParameters_Exit
End If
i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P1 = !FieldParams
GoTo GetParameters_Exit
End If
P1 = Left(!FieldParams, i)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P2 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P2 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P3 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P3 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P4 = Right(!FieldParams, Len(!FieldParams) - j)
GoTo GetParameters_Exit
End If
P4 = Mid(!FieldParams, i, i - j - 1)

GetParameters_Exit:
.Close
End With
Set ParamSet = Nothing
Set MyDb = Nothing
Exit Function

GetParameters_Err:
P1 = 0
P2 = 0
P3 = 0
P4 = 0
MsgBox Err.Description
Resume GetParameters_Exit

End Function

All this works fine except.... How can I find which query is running so that
I can pass the correct queryname to the GetParameters Function

Any help gratefully recieved

Thanks

Phil
Mar 13 '07 #1
3 2140
I would pass the name of the query as an argument. I don't think you
can reference the calling proc.

Mar 14 '07 #2
On Tue, 13 Mar 2007 22:54:58 -0000, "Phil Stanton"
<ph**@stantonfamily.co.ukwrote:

Hi Phil,
I'm trying to follow your message. "Address:GetAddress" is the name of
a query or is it the name of a function?

I don't understand the looping in function GetParameters. Why can't
the query be a parameter query taking the QueryName as an argument,
and thus find the one and only record in the table?

Looking for commas? Use the Split function to tokenize your string.

I'm confused how you would not know what query to run.

-Tom.
>I have a number of queries which use code for the output of 1 or more
fields. For example
Address:GetAddress(AddressID, True, 60)
Address ID Points to an Address in a table - Address Line1, Line 2, Line 3,
Town, County, Post Code, Country.
The True is whether to include the country and the 60 is the maximum line
length (after which a line feed is inserted). All this is to output to a
formatted RTF file for a handbook.

In addition, I have a table that can also hold parameters so for example
if I am running Query 1, I want Address:GetAddress(AddressID, True, 60)
if I am running Query 2, I want Address:GetAddress(AddressID, False, 40)

The essentials of this table are QueryName ( The query or subquery that is
calling the function) i.e. Query1 or Query2
Field Name (The field that
calls the function) i.e. Address
NewParameters
i.e. -1,60 or 0,40

The Function GetAddress first calls a routine to get up to 4 new
parameters.

Function GetParameters(QueryName As String, FieldName As String, Optional
P1, Optional P2, Optional P3, Optional P4)

Dim MyDb As Database
Dim ParamSet As Recordset
Dim QDF As QueryDef
Dim i As Integer, j As Integer

On Error GoTo GetParameters_Err

Set MyDb = CurrentDb
Set ParamSet = MyDb.OpenRecordset("QQueryParameters")
With ParamSet
Do Until .EOF
If !QueryName = QueryName And !FieldName = FieldName Then ' Right
record
GoTo ExtractParams
End If
.MoveNext
Loop

GoTo GetParameters_Exit

ExtractParams:
If !FieldParams = "" Then ' No parameter
GoTo GetParameters_Exit
End If
i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P1 = !FieldParams
GoTo GetParameters_Exit
End If
P1 = Left(!FieldParams, i)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P2 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P2 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P3 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P3 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P4 = Right(!FieldParams, Len(!FieldParams) - j)
GoTo GetParameters_Exit
End If
P4 = Mid(!FieldParams, i, i - j - 1)

GetParameters_Exit:
.Close
End With
Set ParamSet = Nothing
Set MyDb = Nothing
Exit Function

GetParameters_Err:
P1 = 0
P2 = 0
P3 = 0
P4 = 0
MsgBox Err.Description
Resume GetParameters_Exit

End Function

All this works fine except.... How can I find which query is running so that
I can pass the correct queryname to the GetParameters Function

Any help gratefully recieved

Thanks

Phil
Mar 14 '07 #3
Thanks for coming back, Tom

GetAddress is the name of a function which combines and formats the address
lines, Town, County, Postcode etc and returns the field Address

ie Function GetAddress(AddressID as Long, IncludeCountry as Boolean,
LineLength as String) as String

Dim MyStg as string

MyStg = AddressLine 1 & Town & Blah Blah
If Len(MyStg) LineLength
do all sorts of things
end if
GetAddress = MyStg
Exit function

The reason for adopting this approach is that I don't want users to modify
the parameters in the query. So I give them a form that allows then to enter
new parameters with checks that the right number of parameters are enterd
and will get round to checking the values are sensible. This should allow
them to format the output for whatever size of booklet we need to produce.

The QQueryParameters look like this

ClubID QueryID FieldName FieldParams ParamDetail QueryName
6 5 Phone 70 Line length LstHomesFull
6 2 FormatAdd 70 Line length LstHomesWFYC
6 2 NameContact 70,2 Line length, 0 = Separate Line: 1 = each number
on line: 2 = Numbers combined LstHomesWFYC
6 3 Boats -1,-1,2,90 Main Boat, Show Boat Class, 1 = Feet 2 = Meters,
Line Length WhoseMainBoat
Each query could contain more than 1 output field that need parameters to be
modified.

This is part of a Club database. Actually there are about 5 clubs involved,
same Front end, different back ends for each club.
Each Club wants their handbooks in different formats, and it is getting a
pain having a different set of queries for each Club so that each get the
information they want in the format they want. Hence this QQueryParameters
which holds the parameter information for each Club.

Thanks for the tip about Split - never used it - will give it a go

Thanks

Phil
...
"Tom van Stiphout" <no*************@cox.netwrote in message
news:7q********************************@4ax.com...
On Tue, 13 Mar 2007 22:54:58 -0000, "Phil Stanton"
<ph**@stantonfamily.co.ukwrote:

Hi Phil,
I'm trying to follow your message. "Address:GetAddress" is the name of
a query or is it the name of a function?

I don't understand the looping in function GetParameters. Why can't
the query be a parameter query taking the QueryName as an argument,
and thus find the one and only record in the table?

Looking for commas? Use the Split function to tokenize your string.

I'm confused how you would not know what query to run.

-Tom.
>>I have a number of queries which use code for the output of 1 or more
fields. For example
Address:GetAddress(AddressID, True, 60)
Address ID Points to an Address in a table - Address Line1, Line 2, Line
3,
Town, County, Post Code, Country.
The True is whether to include the country and the 60 is the maximum line
length (after which a line feed is inserted). All this is to output to a
formatted RTF file for a handbook.

In addition, I have a table that can also hold parameters so for example
if I am running Query 1, I want Address:GetAddress(AddressID, True, 60)
if I am running Query 2, I want Address:GetAddress(AddressID, False, 40)

The essentials of this table are QueryName ( The query or subquery that is
calling the function) i.e. Query1 or Query2
Field Name (The field that
calls the function) i.e. Address
NewParameters
i.e. -1,60 or 0,40

The Function GetAddress first calls a routine to get up to 4 new
parameters.

Function GetParameters(QueryName As String, FieldName As String, Optional
P1, Optional P2, Optional P3, Optional P4)

Dim MyDb As Database
Dim ParamSet As Recordset
Dim QDF As QueryDef
Dim i As Integer, j As Integer

On Error GoTo GetParameters_Err

Set MyDb = CurrentDb
Set ParamSet = MyDb.OpenRecordset("QQueryParameters")
With ParamSet
Do Until .EOF
If !QueryName = QueryName And !FieldName = FieldName Then ' Right
record
GoTo ExtractParams
End If
.MoveNext
Loop

GoTo GetParameters_Exit

ExtractParams:
If !FieldParams = "" Then ' No parameter
GoTo GetParameters_Exit
End If
i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P1 = !FieldParams
GoTo GetParameters_Exit
End If
P1 = Left(!FieldParams, i)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P2 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P2 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P3 = Right(!FieldParams, i + 1)
GoTo GetParameters_Exit
End If
P3 = Mid(!FieldParams, j + 1, i - j - 1)
j = i

i = InStr(i + 1, !FieldParams, ",") ' Look for comma
If i = 0 Then ' Just 1 paramater
P4 = Right(!FieldParams, Len(!FieldParams) - j)
GoTo GetParameters_Exit
End If
P4 = Mid(!FieldParams, i, i - j - 1)

GetParameters_Exit:
.Close
End With
Set ParamSet = Nothing
Set MyDb = Nothing
Exit Function

GetParameters_Err:
P1 = 0
P2 = 0
P3 = 0
P4 = 0
MsgBox Err.Description
Resume GetParameters_Exit

End Function

All this works fine except.... How can I find which query is running so
that
I can pass the correct queryname to the GetParameters Function

Any help gratefully recieved

Thanks

Phil

Mar 14 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Bob Van Der Ploeg | last post by:
Which would be faster a single select * from table where part in (A12345,1FB2356,346C79,349872) or multiple select * from table where part="A12345" select * from table where part="1FB2356"...
5
by: Shanmugasundaram Doraisamy | last post by:
Dear Group, We have a java front-end for postgresql 3.4. When we monitor the system usage using top we find couple of postmasters taking up close to 90% of the CPU time in total. I would like to...
2
by: ANSWER | last post by:
Hi, I want to make, or is is possible to make database with function to update words in saved document. For example, in my database I enter word in German and English, and when when I press...
2
by: danthrom | last post by:
I have this update query UPDATE LEFT JOIN ON .=. SET . = . WHERE .=.; Which updated the storage date for all linked data with the similar box number. Now I want to create a SQL that will...
0
by: kim | last post by:
Hi, i'd like to know how control which server is actually running between mysqld and mysqld-max. How could i be? Thanks a lot, Kim -- kim
0
chanderravi
by: chanderravi | last post by:
Hello Is there a way to find an application window reference which is running, with its partial name? I can get the application address using a API "FindWindow". It requires two parameters. One is...
8
by: nsrikak | last post by:
Hi, I am Naresh. I am new to jsp technology. In my application I need to transfer controle and perameters from JSP to perl program which is running in other port. I dont know how to implement...
2
by: ruqiang826 | last post by:
hi I have written a service running backgroud to do something in linux. unfortunately,I deleted the source code by mistake, and I can still see the process running background using "ps aux"...
1
by: sganeshsvk | last post by:
sir, Suppose we want to share the two Linux system in mysql database then we using GRANT ALL PRIVILEGES query for access permission between two System Databases. Likely i want to share the...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.