General :: String Fields - Add Next Condition To Query
Mar 17, 2015
I have a query which working fine:
Code:
strSQL = "Select [nazwa], [id_wycena_pre], [Format_(X)], [Format_(Y)], [kolory p], [kolory t], [nazwa_id], [iloscstron], [numerarkusza], [naklad_pracy], [Kolory P], [Kolory T] from tblGoraZleceniaNowaWyceny where [id_wycena_pre]=" & Forms!frmWycenyObszarRoboczy!ID_wycena_pre & " and [nazwa]='" & "środek" & "' or [nazwa]='" & "okładka" & "'"
Set rst = CurrentDb.OpenRecordset(strSQL)...
But now I would like to add next condition to this query
Code:
where [kolory p] is not null or [kolory t] is not null
- these fields are string
I don't know how to write this condition and add to my exiting strSQL string
View Replies
ADVERTISEMENT
Oct 3, 2007
Hey guys-
I have 2 tables- and need to find the record matches between them via their product code. Table 1 has the code as follows (and is considered 'the Master List')-
xxx-xx-xxx Where x is an integer.
Table 2 has split the 3 sections of the code out to 3 individual fields, like this-
Field1 Field2 Field3
xxx xx xxx
I need a query that will combine the 3 fields in table 2 to the same format as Table1 so that I can find the matches between them. I know this is a basic question, but I'm a learning newbie. How would I write that in a query? I don't think I want to write the format to the table- just include it in the query somehow...
Thanks!
View 6 Replies
View Related
Nov 5, 2012
How to get the last character of the string in query?
I have a table name PlateNo and I want to get the last character for registration purposes.
Ex. ZMD-123 - I want to get the number three(3) and if its possible every time the user types the last digit in criteria it will show all plate number ending in the entered number.
View 5 Replies
View Related
Jan 26, 2005
Hi can anyone help me with this query?
'SQLTable "Resolutions Per Year", "SELECT ResName, COUNT(ResName) AS Total FROM Stats LEFT JOIN Resolutions ON (Stats.UserID = Resolutions.UserID AND Stats.ResID=Resolutions.ResID) WHERE Stats.UserId = '"&login&"' AND Date LIKE '%sYear%' GROUP BY Stats.UserID, ResName ORDER BY COUNT(ResName) DESC"
sYear is a variable
example sYear = 2003,2004,2005....
This does not work.
Is there anyway to assign the search string to be a variable?
View 2 Replies
View Related
Apr 27, 2008
I’ve created a report containing two labels and two text boxes. The “Record Source” for the report is Tbl_MasterList2. The control source for both text boxes is named field4 and field5, which are based in Tbl_MasterList2. As a result, my report displays all records from Tbl_MasterList2, field4 & field5 including those, which are blank.
How do I write an expression in my report that will allow me to display the values in field4 if filed2 = stringXYZ?
View 4 Replies
View Related
Jun 22, 2015
While processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.
I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"
Is presented in Group by as: "ç …5"
I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get that same results.
File Corruption? anything to do about it?
View 12 Replies
View Related
Jul 29, 2015
Windows XP
MS Access 2007
VBA experience is very limited
So in my forms 'BeforeUpdate' property I want to validate whether a condition is met. The condition is whether 'any one of 4 fields contains something (text or number)'. I have written this code for validating a single condition and it alerts if the field is left blank. This code returns a warning message when the field is blank.
If Me.[txtObjective] & "" = "" Then
MsgBox "A 'Problem Objective' is required, please complete this field.", vbOKOnly
Me.[txtObjective].SetFocus
Cancel = True
[code]...
how to write the SetFocus part. And I dont know if this is correct anyway.
View 6 Replies
View Related
Feb 25, 2013
I need to make a filter by using Macro in Access 2010..So in "Where Condition" I should put an expression belong to the poth of the fields. I now how to use "Where condition" but only for one field like below :
Code:
="[TabReq_No]=" & [Text2]
View 7 Replies
View Related
Jun 4, 2014
How will I make a IIF formula with sum if 3 conditions are are true. I am trying to make a query which sum the Lbs Produce if date, Product Id and location met.
View 6 Replies
View Related
Jul 24, 2013
I'm working on a bound form and I'm trying to set the where condition of a DSum to Month(ReviewDate) = Month(me.txtReviewDate), but having trouble. I have tried several different ways. I am trying to sum all of the records from the recordset where the month is the same. So if I am on a July record, for example, I want to see a sum of all July records only, no other months. When I navigate to an August record, then the DSum should provide the sum of only August records, and so forth.
Code:
Me.txtRecordsforCurrentIndividualforCurrentMonth = DSum("NumberofPresentations", "tblCCMECPresentation", "ReviewDate = #8/1/2013#")
But as soon as I try to replace the actual number (#8/1/2013#) with a variable, I run into trouble.
View 5 Replies
View Related
Jun 26, 2015
I have a field named Poisition_Id and a yes/no field with the name Hired, what i want is dcount under condition initially with Position_ID to Position_ID then to see the field of hired if "yes"
Example:
=dcount "a" see in tbl_open with criteria position_id = position_id with criteria hired=yes
I am unable to write this formula..
View 1 Replies
View Related
Apr 10, 2013
I have a macro set to email a form to a person once a condition is met. This works fine but I've discovered that Outlook (2010) has to be open BEFORE this macro is invoked.
I wanted to add a step to the macro before the one that makes it email that basically says to open Outlook.
I have tried the RunApplication macro, but I apparently am not getting the path right. I don't understand what the problem is.
I accidentally dragged Outlook to my desktop and therefore created a shortcut from my Start Menu, but now when I go to Microsoft Office in my Start Menu, Microsoft Outlook isn't one of the options. I can't find the path to where it may reside now.
I am using the path to the shortcut on my desktop.
C:UsersJust MeDesktopMicrosoft Outlook 2010
What else am I supposed to do?
View 8 Replies
View Related
Mar 1, 2006
i have a Yes/No checkbox to indicate whether a job has been done.. if it is done then i want it removed from the list.. so i think my query sql needs to be something like..
note..the bold bit is the bit i am questioning.
WHERE (((Booking_Main.Job_Date) Between Date() And 1+Date())) AND Booking_Confirm.Job_Done ="No"
or something like this??
View 3 Replies
View Related
May 13, 2015
I am currently running queries from a table and I get a list of part numbers and the totals. I would like to collate this into one row for each material instead of multiple - so essentially it would be like using a pivot table and summing the totals.
Example - currently looks like this:
Material Total
F12 33
F12 21
F12 5
F13 44
F13 60
But I want the query to produce this:
Material Total
F12 59
F13 104
View 14 Replies
View Related
Jun 21, 2005
hi ppl,
Could someone please help me with a query that I'm trying to create.
I need to create a query where once the DATE is overdue/expired how can i turn it into a red colour or something just to separate it from others.
e.g:
Length = 3
DateCleared = 31/01/2001
DateRenewal = 30/01/2004
--- obviously this date has expired and now i need to make it visible that it has expired. So I was thinking either turn it red.
thankyou.. :( :confused:
Ps. In order to create DateRenewal I used a formula by using the Length to calculate three years from the DateCleared.
View 3 Replies
View Related
Jan 9, 2006
Hi there, I currently have the below tables:
tbl_stores
ID NAME
123 Store A
456 Store B
tbl_returns
ID NAME RETURN
123 Store A 9999
123 Store A 8888
789 Store C 9999
I want to find the stores that exist in the stores table, but not in the returns table WHERE the RETURN is equal to 9999. This would hopefully return 456 Store B
My problem is when I put a condition in my query it returns nothing as the condition is based on the left joined table.
SQL
SELECT tbl_stores.store, tbl_stores.name
FROM tbl_stores LEFT JOIN tbl_returns ON tbl_stores.store = tbl_returns.store
WHERE tbl_returns.store Is Null AND tbl_returns.survey_id=2
GROUP BY tbl_stores.store, tbl_stores.name, tbl_returns.store
Any ideas? Thanks for your help with this one!
Cheers
Tony
View 2 Replies
View Related
Feb 4, 2008
Im trying to build a query that checks four fields for blanks. If I try to run the query with the criteria Is Null for all four fields, I only get 27 records. However, if I run the query with the condition only on one field I get the right number of records around 1000. I have been having to run four independant queries and then merge them in Excel. What am I doing wrong?
View 7 Replies
View Related
Nov 16, 2007
I have a txt file that I'd like to import, which contains answers to a test. The number of questions on the test varies year to year, but this year there are 110 (split in to two sections).
I'd like to import the file, so each answer is a field, and then score the answers against a 'correct' answer lookup table.
What's the best way of approaching this? I first thought of using vba to read the text file, split the text, and then use an SQL command to insert to to my table. This seems tedious (there are around 6500 respondents).
Anything else I could do instead?
Thanks,
Antro.
View 4 Replies
View Related
Jun 15, 2006
I have a database with 3 tables. Each table has an account number field however the tables are completely different as are the account numbers that populate them. To go with the tables are queries that will bring in desired information. What i would like is for the user to input an account number on a form and have some code in the background that will find the account number in the table it is in and open the corresponding query. Is this possible? Thanks for the help. I have been trying to write code with DLookup but can't seem to get it to do what I want.
View 7 Replies
View Related
Mar 25, 2008
Hello,
I've faced a - for me - tricky problem when I've created a query from code. I have an MS SQL backend and in it there is a table which has a long time format field. I want to make an sql query where the condition is a time data. Unfortunately when I enter a time which is surely in the table, there is no result of the query. I've tried to solve it on several ways, time formatting, etc., but I had always the same result, to wit no result.
Does anyone idea, how could I solve it?
Any tips are appreciated very much!
Many thanks in advance!
VoiD:confused:
View 1 Replies
View Related
Feb 26, 2014
I am trying to copy notes from one table to another table where a condition has to be met and I can't figure out how to do that in an APPEND query.
Each record has a unique number that comes into my Initial Table in my Access database from a construction program I download. In this particular table both the [DNJTNo] and [DNVersionNmbr] fields can have duplicate numbers. However, when I run the query I want it to add a record to the second table only after the query checks that the [DNJTNo] in combination with the [DNVersionNmbr], is not already in the table. If it is, I do NOT want it appended to the second table. (I have attached a picture of the query in APPEND design stage).
View 2 Replies
View Related
Oct 24, 2013
Is it possible to set any label id as query parameter in sql code or access?
View 5 Replies
View Related
Aug 23, 2013
I am creating a search function to search a name. The DB stores it as [First Name] , [Last Name].... This query works
Code:
SELECT *
FROM tblVolunteers
WHERE ((([First Name] & " " & [Last Name]) Like "*bob j*"));
It will show me records for Bob Johnson, Bob James and Bob Jones....
I am trying to duplicate this with a DoCmd.OpenForm statement... Replacing the static name with a field variable...
Code:
Dim whereClause As String
whereClause = "[First Name]" & " " & "[Last Name] Like " '*" & volunteerName & "'*""
DoCmd.OpenForm "Volunteers", acNormal, , whereClause, , , False
View 4 Replies
View Related
Aug 13, 2013
Currently using Access 2007-2010. I have a text file that I am trying to import into Access. I am having issues with the time fields. I need to be able to sum them in a query or report. When I import it in, Access is put the 12:00:00 am as default and adding numbers to achieve the 11 character field. I need to be able to sum these fields later in the database. The file looks like this:
ACD Calls Avg ACD Time Avg ACW Time ACD Time
Totals 103 3:49 1:07 6:33:45
2 1:25 :00 :02:49
8 2:02 :00 :16:15
2 3:17 3:06 :06:34
17 4:49 6:06 1:21:53
View 1 Replies
View Related
Jan 14, 2014
I am currently creating a database for lift inspections. The output is a mail-merge report. What I am trying to do is get the multiple Lift Name (child) and Floors Served (child) combined into a string so that each Lift Group (parent) inspection has only one row. This then means that each lift group inspection will be shown as one record in mail merge.
Current
Lift Group ID - Date of Inspection - Lift Name - Floors Served
34 - 14/01/14 - Lift 1 - G-1
34 - 14/01/14 - Lift 2 - 1-3
35 - 14/01/14 - Lift 3 - G-8
34 - 14/01/13 - Lift 1 - G-1
34 - 10/10/13 - Lift 2 - 1-3
Desired:
Lift Group ID - Date of Inspection - Floors Served
34 - 14/01/14 - Lift 1 G-13; Lift 2 1-3
35 - 14/01/14 - Lift 3 G-8
34 - 10/10/13 - Lift 1 G-13; Lift 2 1-3
View 14 Replies
View Related
Aug 19, 2014
I have to three fields in my query wizard, lets call them A and B and C. The condition for the query is either A or B is Null and C is Null. Im not sure how to do this though becuase A, B and C are different fields. I wrote
Code:
Is Null
.
under the criteria of A and B and C, but this specifies that A,b and C is Null, how can I make it so that C must be Null and either A or B or Both.
View 1 Replies
View Related