Creating SQL Queries From Strings (VB)
Dec 14, 2005
Does anyone know how to create a query object using a string that contains SQL code?
The reason I ask is that I want to be able to run an ad hoc query, but I can't use a RecordSet because there's no way (that I know of) to take the results of a recordset and create a query display out of it (meaning, make it look like a query was executed)
So what I really want to do is to create a query, and then execute it with "DoCmd.OpenQuery()" If that's possible. Any ideas?
View Replies
ADVERTISEMENT
Jun 6, 2007
Hello
I have two tables with names of people in different forms.
table1 : [name] can be either in the form of :
- firstname & firstname surname
- surname, firstname
table2: [surname]
I'm trying to select all the records in table1 where [table2]![surname] is part of [table1]![name]
I've tried using
Like "*"&[surname]&"*"
This returns everything rather than the similar names.
I think this should be simple but can't work it out.
View 1 Replies
View Related
Oct 9, 2014
I have four columns: date1, time1, date2, time2. All are strings.I want to concatenate date1 & time1. Then date2 & time2.Then I want to do the following calculation and the answer to expressed as number of days:
datetime2 - datetime1.
I am using MS Access 2007 and the results are being grouped by a different variable. Ideally I want to complete this action as one complete statement in the SELECT statement of my query. This is very easy to do in Excel but I'm baffled by MS Access!
View 2 Replies
View Related
Oct 12, 2014
I have a Db with a table imported from sql server. One of the fields contains data wrapped between xml tags. The xml tag identifiers when looked at as columns are not consistently the same tag identifiers but effectively I am parsing the string to provide columns of data from between the tag identifiers.
example:
<T6><A29>Compton Group</A29><A31>CO009</A31><A32>CO161/71</A32><A30>Invu Proposal</A30><A28>05 September 2007</A28><A41>Sales</A41></T6>
I am wondering if the method I am using to deconcatenate the string is the bext way. In my query I reference the left tag value .
TagVal1: ParseTagOpenVal([Attributes],3)
And then I parse the result based on the right hand tag value
Val1: ParseTagCloseVal([TagVal1],1)
I have a couple of modules for parsing the string and I am able to deconcatenate using this method but I am wondering if its the only way or the most efficient?
View 1 Replies
View Related
Aug 25, 2014
I need to create a query that strings data horizontally that corresponds to the primary key.
Example of what I am looking for
Fields:
Part_No,
Mfg_No,
Mfg_Name
Query Columns: Part_No, Mfg_Info (Mfg_No: Mfg_Name)
Also if the part_no (primary key) has multiple values I need the data to string horizontally like this:
Part_No, Mfg_Info (Mfg_No1: Mfg_Name1),
Mfg_Info (Mfg_No2: Mfg_Name2).......
View 14 Replies
View Related
May 1, 2013
Is it possible to run a basic select query to pull records based on multiple strings entered by a user?
I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.
I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.
But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above
SQL statement (Like) is below :
SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));
View 10 Replies
View Related
Jun 23, 2015
I have 3 select queries which Im trying to output to a combo - Ive tried a UNION query but I get an error
ODBC-- call failed ODBC Driver SQLBase.....
Firstly is do the results need to match within a union query? I mean they have no relationship what so ever Im just trying to populate this combo with the same results.
Secondly is there a better way to do it? 2 of the select queries query a linked SQL table and the third is a local table. All of the select queries work on their own.
View 2 Replies
View Related
Aug 17, 2006
Hi All,
I want to create a form with a Command button, when we click on the command button it needs to bring the Query from a particular mdb, the path for this mdb will vary so we should be able to provide the path info, after bringing the query it needs to be run, how do I do this by just clicking couple of Command buttons?
Thanks
View 2 Replies
View Related
Aug 9, 2007
I have split my database and have the BE on a network server. Users were each given a copy of the MDE file to enter data from using a Form.
When I bring up my copy of the MDE and create a query or edit an existing one and give it a new name... only I can see this new query. Is this the way it's supposed to work?
What would I need to do if I wanted the query I created to be seen by everyone who has access to the database? Do I need to create the query on the MDB and then create a new MDE?
Thanks,
Michael
View 5 Replies
View Related
Nov 8, 2005
Hi,
I have 2 strings:
str1 = SELECT Field1, Field2, Field3, Field4 WHERE (Criteria1)
str2 = SELECT Field1, Field2, Field3, Field5 WHERE (Criteria2)
I want to show in listbox only "Field1" from str1 and str2.
How unit 2 strings?
Thank You in advance.
View 4 Replies
View Related
Mar 31, 2006
Hi all.
I have 2 strings:
strSQL1 = SELECT FieldA FROM table WHERE FieldA = xxx
strSQL2 = SELECT FieldB FROM table WHERE FieldB = xxx
I need unit this two strings to third string strSQL3.
I must have result StrSQL3 = SELECT FieldA, FieldB FROM table WHERE FieldA = xxx and FieldB = xxx
Is it possibly unit strings (not Select Query)?
View 4 Replies
View Related
Nov 3, 2007
So I need to know this. How do I:
*Add 2 text fields together with a space between?
*Truncating field 1 to the first character and field 2 to the two first characters?
Sorry for my noob questions :D
View 6 Replies
View Related
May 23, 2005
Hi. I am trying to compare 2 string values.
5:00
5:45
9:54
10:15
Lets say those are my values. Well in the formula >"5". 10 Will not show up correctly. Does anybody have a piece of code or something that will correctly display 10 as >"5". Any help greatly appreciated. Thanks
View 3 Replies
View Related
Aug 23, 2005
Seems like a simple question but I am stuck.
I need to join two strings from different records in a table
table eg
ID Desc
1 blah blah
1 more blah
2 blahring
2 blah
3 and last one
I want to join all the "Desc" strings that have the same ID to get
1 blah blah more blah
2 blahring blah
3 and last one
Can anyone help?? :o
Thanks
Paul
View 1 Replies
View Related
Dec 15, 2005
I am normalizing data from a spreadsheet of just over 4000 records. The spreadsheet has a "Model" field that contains both the model number and a model description, separated by a space. I would like to split this field into two different fields: "Model_Number" and "Model_Description". The model number is one word of varying lenths.
Any ideas?
View 5 Replies
View Related
Nov 2, 2006
Hello,
I am having trouble with a query. I have a number of blanks for a field and I tried using the expression from the help:
=IIf(IsNull([fieldname]),"Unknown",Format([fieldname],"@;LS"))
But my result set is still a lot of blanks.
This is the expression I was trying to use:
IIf(IsNull([tblMainpersondata]![MailingAddrLine2]),[tblMainpersondata]![MailingAddrLine1],[tblMainpersondata]![MailingAddrLine2])
On most of these, the result was a blank, instead of MailingAddrLine1. If there was visible data for MailingAddrLine2, it appeared.
Any help or suggestions would be greatly appreciated!
View 2 Replies
View Related
Jan 29, 2007
Hey Guys,
Just wondering how I would go about comparing two strings, and spitting the results out a similarity percentage?
E.G. (String 1) Postal Address: "11 John St"
(String 2) Street Address: "11 John Street"
(Output): 80%?
Cheers
View 5 Replies
View Related
Jul 10, 2007
Hello,
I would like to put a restriction on the SQL statement such that if one particular field contains the character "MO", then we take in the record, else we ignore them. May I know how could this be done in the "WHERE" part of the SQL statement? I do not think SQL would read things like Instr(). Thanks!
Regards,
Anyi
View 13 Replies
View Related
Nov 15, 2004
Hi all,
Please can someone help. I am trying to pass a query to the database from vba but can't seem to get the string concatenation of fields correct.
Clinical area and title of audit are database fields. Any ideas please? Thanks!
Here is what I have:
StLc = "[Clinical Area] = """ & Me![Combo309] & """ & """ And """ & [Title of Audit] = """ & Me![Combo309] & """"
View 1 Replies
View Related
Dec 18, 2004
Hoi,
I have a loop where i use a string (strCount). The first time I run the loop strCount has de value A. The second time strCount must become B, the third time C, etc
I already tried to strCount = strCount + 1 but it did not work
does someone know how to do this?
greets
Koen
sorry formy wad english
View 2 Replies
View Related
Feb 9, 2005
This is a database that was handed off to me for upgrading.
The person who initially created it made the Name field in the table contain the entire name... So a single entry in the field looks like
CHRIS R LOUNSBURY
The table is in the attached table. This is merely a sampling. The main table has over 3,000 names in it. Is there an easy (or maybe not so easy) way to automatically parse that data and split the names into their own fields.
An example would be the string CHRIS R LOUNSBURY. Run code which takes the string left to right to the first blank space, and splits it off into First Name field. Take the last part of the string (right to left) to the first white space and put it in the Last Name field.
Is this possible? Or do I need to find myself a temp employee to data entry all this over again
View 14 Replies
View Related
Oct 1, 2007
Hi all
I have a Row source string which I use quite often. Currently, I declare it in each procedure and copy and paste it in. The problem is when it needs changing, I need to change it in a billion places.
can I declare the string as public and set it as public too so that I can just make a reference to it rather than copy and paste entire slabs of code into each event??
perhaps I need to place it into a module?
Suggestions are much appreciated.
View 12 Replies
View Related
Aug 9, 2005
I am trying to create a new query and a new report in my database. The program will not let me. The "new" is grayed out. Can anyone help me? :confused:
View 14 Replies
View Related
Mar 20, 2008
I want to keep the user away from the tables, but want them to beable to query the tables on their own from a selection on the Main Swichboard. My question is twofold, one being is this advisable and two how would I go about that? I can't seem to find anythng on it in my searches.
View 5 Replies
View Related
Feb 15, 2006
I need help setting up some basic queries on my database, i dont really know what fields im meant to be querying so help would be great.
Sample data Included in attachment.
In the sample, seconds is the time step, so 5 minutes would be 300 seconds.
Queries i need to create:
Query 1 - Number of spill timesteps >0.001
Query 2 - Total number of timesteps
View 3 Replies
View Related
Nov 1, 2006
Access does not have an option to sort alphanumeric strings properly with the result that sorting a column with the following (sort of) data is almost impossible.
Flat A1
Flat A10
Room A13
Room A2
Room A21
B5
Flat 5b
10k1
10 k3
10 k12
10 k20
10 k2
etc
I need to be able to get addresses sorted correctly and they always have a mixture of alphabetic and numeric characters.
The steps I have followed to try and achieve this are as follows:
Create a matchfield containing the data for sorting (typically the street number followed by the flat number/name)
Split the matchfield into separate fields where there are spaces. "Room A13" becomes "Room", "A13"
Then split the fields/columns by separating the alphabetic and numeric portions - i.e. "Room A13" becomes "Room", "A", "13". This I have not been able to achieve successfully.
(Thinking about it the first step of separating by spaces is probably not necessary. All that is needed is to separate the numeric and alphabetic data)).
Once you have separated the data into alaphbetic and numeric content a sort is straightforward.
Why can't Access cope with what I would regard as a fairly basic requirement (i.e. to be able to sort alphanumeric strings correctly).
Does anyone have a suggestion how I can solve this problem until Access is improved?
View 11 Replies
View Related
