I have a table in which one field badly needs separating into two. Currently, it is laid out as a mixed case text string followed by an upper case text string, e.g. O little town of Bethlehem FOREST GREEN.
I need to move the upper case string (FOREST GREEN) to a new field (and then trim the original field accordingly). Since there is no delimiter between the two parts of the field, I have no idea how to proceed. Can anyone help?
Hi Everybody, I have an access database and in a certain table I have a string field which has two values. I want to make a query that will show all the fields which contain a substring.
I'd better give an example:
Table: Kid
Fields: Name: Ben Activities: Soccer, Tennis
Name: Dan Activities: Soccer
Name: John Activities: Football, Origami
. . .
I want to show in a query all the Names which have a "Soccer" substring in their Activities string field
I'm a rank beginner in Access, and apologize in advance for such an elementary question, but I need the basic syntax to run a Delete Query on the substring of the first character in a field. The question is #8 in Shelly Cashman's Office 2003 Course One p.AC168, Delete all records where Description starts with S.
p.AC137 deals with Delete Query, but only covers deletions based on whole fields. I tried 'Help', but ended up at a Microsoft site offering me a 30MB file on SQL. I may well get deeper into Access after a more advanced course, but this one is for beginners.
TIA for any help with this.
~~~Peter.
PS I thought I created a profile with an alias, but I seem to have ended up with pvmoor@verizon. instead. Oh, well.
I'm using Access 2010. I'm passing a string into the OpenArgs of my report - works fine. In the report there are 3 rich text fields which may contain the text I passed in, and if so I want to change the color of that text to red so it stands out.
The value passed to the report changes so I'll need to use VBA in the detail's format section to check each of the 3 rich text fields.
I need to identify and parse (substring) a number of digits from a barcode. The barcode digit count will vary, based on the number of details included in the barcode.
For example: the barcode for GS1 DataBar begins with a 4 digit code of 8110 identifying the code as GS1. The 5th digit then identifies the number of digits directly following which constitute the manufacturers code, which can range anywhere from 6 digits to 12 digits. I need to pull the number of digits out of the barcode based on the value of the 5th digit placeholder and pull only that many digits from the string and replace them into another field. There are potentially 70 total digits in the barcode. I have only provided the first section, which I am asking the question on.
Simply put: I need to tell the substring to identify the number to parse from digit 5, (which will have a min of 6 digits and a max of 12)
How do I pull the information out of a string to create a substring in another field by first defining the number of digits to pull out (Digits 6 to 17 min of 6 digits and a max of 12 digits) based on a value within the string (digit 5= value)
So if Digit 5 is 6 the returned value would be: Digits: 6 7 8 9 10 11 (6 6 6 6 6 6 )
If Digit 6 is 9 the returned value would be: Digits: 6 7 8 9 10 11 12 13 14 (9 9 9 9 9 9 9 9 9)
The first portion of the barcode is below: There are other pieces necessary to be pulled the same way, but from other sections, and based on yet another X desinator field (the next with a min of 1 and max of 5) I assume the pull would be similarly completed.
I don't know how to write code, I have used formulas but not this detailed or specific.
Ex: I know = Min (barcode, 5, 6) would pull the 6 digits after the 5 spot, but how do I vary the 6 (count) number in the formula?
Example below: No additional spaces, dashes, letters etc. (A's = 1-4, X = 5, M's = 6-17)
I have a client that wants to enter a range of dates in a query of when they will call that person back. Then they want to be able to type in a range of dates and have a make table query show them all the people that fall in between these two dates....is this even possible??? Ex. Joe March 3 to March 8 Mary March 4 to March 9 John March 5 to March 10
So if they type into the query March 3 to March 6 all three people should show up because one of the dates specified lies within the parameters they are asking for.....man I am out of ideas
As part of a larger issue, I am trying to figure out how to have an Access 2000 select query produce all dates in a date range into one field on the query. Assume at this point there are no tables in the query.
If 01/01/06 (mm/dd/yy) and 02/01/06 is used, then it'd list all the dates between those two, inclusive.
I'm trying to figure out how I can set a parameter on my query to search for any activity that occurs within a month, that falls within a date range. Specifically:
Criteria TextBox: 4/2006
Activity Start: 3/6/2006 Activity End: 5/5/2006
I want this activity to show in my query because the time between start and end occurs during the month of April. It's no problem setting this up for a specific date, ie 4/1/2006, but I don't know how to do it for an activity that occurs all days between 4/1/2006 and 4/30/2006..or for one that starts 4/12/2006 and ends 4/18/2006, or starts 4/12/2006 and ends 5/9/2006. etc.
I have a table of data, one of the fields is a date.
What i want to do is be able to have a query that can check if the date falls within a certain range - ie fiscal year and output in another column the fiscal year "code".
Ie: dates between 01/06/05 and 31/05/06 is fiscal year 0506 dates between 01/06/06 and 31/05/07 is fiscal year 0607
Could this query be dynamic so if a new fiscal year begins it would know to make the output the next fiscal year code???
I have two tables: tblClasses & tblSchedule. There are joined by ClassID. For each class in the tblClasses there are several records with date field in the tblSchedule. (So each class stored in tblClasses happens on multiple dates stored in tblSchedule).
I want to create a query (SQL view) that would take two date inputs from a form(date range): datefrom & dateto, and return any class of which FIRST day of classes falls in between those dates.
(Or: how can I add a field to my query called [First day of class] that would basically have the value of the first date from the tblSchedule for the joined classID?)
Below is a table that I want to look up from a query. Basically I want to look up a value (e.g 3.56) and find which PRA it fits in so in this case 3.56 is between 3 and 6 so the value returned is 0.7. Where it gets complacted is that the 3.56 is generated on the fly in the query. Can I use a dlookup with a range. Ive tried but the syntax is wrong i think. DateDiff_RepToMat is the generated field (3.56)Any help or suggestion are appreciatedx = DLookup("PRA", "tab_ref_MaturityRiskRatings", [DateDiff_RepToMat] BETWEEN [RangeLow] and [RangeHigh])RangeLowRangeHighPRA0 1 0.003 6 0.46 12 0.7
Hi, I have atable with ID ranges like this: ID1 ID2 Logo 1 50 HO 51 75 HY 76 100 GM
I want to know how I would write a SQL statement that would find which logao given a certain input. So for example, If I had th euser enter 53 from a textbox on a form it should bring back HO. Can someone please help me with this. This is the kind of thing that I tryed so far, but does not work:
SELECT Logo FROM tblLogo Where forms!frmTest!Text0 Between(ID1) (ID2);
I need to have grouped UserName, Total count of DocumentID. Then i need to have age range count between 0 and 5, 10 and 15, 20+ .... basically, Username , Total DocumentID count for User, Range Count (0-5,10-15....)
I need to get records between two dates. Here is my query:
SELECT WGMAHIST_TIMEDTY.TDYPT, WGMAHIST_TIMEDTY.TDYCO, WGMAHIST_TIMEDTY.TDYSSN, WGMAHIST_TIMEDTY.TDYFND FROM WGMAHIST_TIMEDTY WHERE WGMAHIST_TIMEDTY.TDYSSN = 464299266 AND WGMAHIST_TIMEDTY.TDYEDT >= #06/29/2007# AND WGMAHIST_TIMEDTY.TDYEDT <= #09/21/2007# AND WGMAHIST_TIMEDTY.TDYPT = 1 AND WGMAHIST_TIMEDTY.TDYCO = 8
I am obviously doing it wrong because I keep getting an error that complains about the key work BETWEEN.
How can I make this work? I am using MS Access 2007.
I need to attribute a value to records between a certain range, example All records that the field "Date" is between 01/04/2007 and 31/03/2008 I need to return a value in a field named "Group" 2008 in my table. Example: 12/02/2003 = 2003 15/03/2003 = 2003 15/04/2003 = 2004 20/08/2003 = 2004 17/12/2003 = 2004 20/02/2004 = 2004 01/04/2004 = 2005
I'm new in this forum and can be considered as beginner in Ms Access. I tried to make a SPC database and currently stuck at calculating the data range for moving range graph. The question is, how to calculate range between 2 consecutive data and then sum it. For example: A, 195 B, 198 C, 196 D, 194
Thus the range between A and B is 3 (198-195), B and C is 2 (198-196), C and D is 2 (396-194). The total range is 7 (3+2+2)
I have a form with a combo box containing the names of the Months. What I want to do is then pass the Month name to a query as a date range. For example, select "May" from cboMonth and the query will check the date field for "between 05/01/2008 And 05/31/2008"
Here is what I have in my query but it does not show any records when I run it:
IIf([Forms]![Form1]![cboMonth]="May",Between #5/1/2008# And #5/31/2008#)
Is it even possible to use this type of an expression in the query?
I have 15 text boxes on a form. After the last text box is filled in, I can get the average okay, the sum okay, but would like to caculate the difference between the highest and the lowest numbers (Range). I've tried a ton of "If" statments, but did not seem to work. Any ideas would be helpful. I searched the forum for "Range" but could not locate an answer.
Hello - I am trying to create a FOrm that will allow the user to type in two dates. From these dates a table will be created and displayed in a List Box.
Does anyone have a very simple example of the code
1. I am trying to write two input boxes to variables. 2. On click I am displaying a message box that repeates the values of these two variables 3. When they hit OK I want the records to appear in the ListBox. 4. From this point I will want to get the data to a report of some kind.
I am very new to this and am trying to see if someone has any code examples....
This is the code I have so far.....Actually I cant get passed #2 above. It shows the message box and the text but not the values of the variables...
I dont think I am using the variables right......I dont understand the syntax needed to write an input box value to a variable......
Please Help....
THanks
CODE:
Option Compare Database
Private Sub BeginingDate_BeforeUpdate(Cancel As Integer)
Dim BD As String
BD = BeginingDate.txt
End Sub
Private Sub EndDate_BeforeUpdate(Cancel As Integer)
Dim ED As String
ED = EndDate.txt
End Sub
Private Sub Command5_Click()
Dim var_BeginingDate Dim var_EndDate
var_BeginingDate = BD var_EndDate = ED
MsgBox "The Variable is " & var_BeginingDate & " and " & var_EndDate, vbInformation + vbOKOnly
I am working with a linked table that has a field [Originated Date]
I have created a query for this table and on the criteria for [Originated Date] I put the following:
Between [Forms]![Date Input]![StartDate] and [Forms]![Date Input]![EndDate]
I am using a pop up calendar to populate [StartDate] and [EndDate] fields on the form.
The format of the Date field in the linked table is "Text" not sure if this is the root of my problem ?
When I run the Query Manually and input the [StartDate] as yyyy/mm/dd and the [EndDate] as yyyy/mm/dd the query returns the desired results; However when I run the query with from the form It does not return the desired results.
It is driving me crazy not sure where the discrepency is ?
I have a table holding the number of each task planned each month, with a field for the name of the task and a field for each month stretching of for a number of years, and would like to be able to open a form or run a query on the present months field and the following six months without having to have a seperate form or query for each starting month.
any help or a point in the right direction would be apreciated.