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?
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!
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?
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));
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.
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?
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?
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
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
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.
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!
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?
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
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??
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:
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.
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.
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?
