Modules & VBA :: Check ID Number Between Range

Oct 23, 2014

I'm trying to create some code that will create a new folder depending on the ID that is currently being added to the database.What happens at the moment is a new ID is generated which in turn is job reference.When this is added to the database a folder is created with a prefix of this ID number and a 20char test specified in a text field by the user.

What I am trying to achieve is this:If the ID = 57...Then a folder is create called 1 - 500 (and ignore if one has already been created, which it should have at ID number 1)...When the ID Number 501 comes along another new folder is create called 501 - 1000 etc etc.The idea is not to have 3000+ folders in just one folder making it look a bit messy and lengthy to look through.

View Replies


ADVERTISEMENT

Modules & VBA :: Automatically Check A Box If Certain Fields Are Within Range Of Numbers?

Nov 2, 2013

I have 3 fields on a form PU_POINTS, SU_POINTS & 2_MILE_RUN_POINTS. The user enters a number 1-100 in each of these fields. If the user enters a number in any of the 3 fields of anything less than 60 then I want it to check a fail box. If the user enters all numbers 60 or more then i want it to check a pass box.

View 2 Replies View Related

Modules & VBA :: Count Number Of Unique Records Based On Range Of Date

Jan 19, 2015

Code:
' count records in query
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim beginDatum As String
Dim eindDatum As String
Set db = CurrentDb

[code]....

View 4 Replies View Related

Modules & VBA :: How To Check If A Number Is Divisible By 6

Feb 1, 2015

is there a function that will let me check if a value that was input is divisible by 6?

View 2 Replies View Related

Modules & VBA :: How To Check For Start Date In Date Range Only Combo Box Value

Jan 22, 2014

I have some code that filters job raised I have 2 text box's txtdatestart and txtenddate after entering. date range between the too text boxs it shows me all job raised with in the period.i have entered what I would like is filter it again by client field using combo box cboclient so if the user enter's client name in cboclient combo box and date range in txtdatestart and txtenddate it will only show jobs raised with in the date range of the client enter in the combo box but if the combo box is empty show.

Code:

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "#mm/dd/yyyy#" 'Do NOT change it to match your local settings.

[code]....

View 6 Replies View Related

Modules & VBA :: Check If Number And Date Of Telegram Is Existed

Jan 20, 2015

i want to check if the number and the date of telegram is existed

the number should be LIKE not equal
the date should be equal

this is what i wrote

Code:
If DLookup("Telegram_Number", "tbl_Violation_Of_Building", "Telegram_Number Like " + Forms!frm_Add_Violation_Building!Telegram_Number + " And Telegram_Date = #" & Format(Forms!frm_Add_Violation_Building!Telegram_Date, "yyyy/mm/dd") & "#") Then
MsgBox ("number existed")

[Code]....

View 10 Replies View Related

Modules & VBA :: Check If A Field With Number Data Type Is Blank

Feb 26, 2015

The DolphinBatchNo has number data type but the following sql statement doesn't capture the ight records. it doesn't check id dolphinBatchNo is blank.

Code:
strsql="select * from `MasterTBL` where PolicyNumber>=" & TxtFPolNo & " and PolicyNumber<=" & TxtLPolNo & " and PolicyStatus='Live' and DolphinBatchNo is null order by PolicyNumber"

View 2 Replies View Related

Modules & VBA :: Check Policy Number In Access Table And Populate Related Data In Excel Sheet

Aug 14, 2014

See attached the Workbook. I need to check the policy Numbers in Column A of all the sheets in the attached workbook if its present in Access Table. If yes then write the corresponding ScanDate and BatchNo from Access table to columns I and J of all the sheets. I need to write VBA code to perform it.

In the attached workook, only Sheet1 contains the data but in actual there will be data in 5 sheets in the workbook.

View 1 Replies View Related

Job (ID) Number Range - To Be Queried?

Jun 11, 2007

Hi all,

I'm building a quick database, but some of the data to go into the database is quite... odd...

Anyway, the point is, I have one sheet that says

"Job Number 1200-1245" which would have all the same details, dates, etc.

Is there a quick and easy way to put in a range, say J1200-J1245, enter the data once, then be able to query... for example J1212 would return the same data.

Cheers :)

View 1 Replies View Related

Value Based Upon Number Range

Apr 14, 2006

I am creating a query which determines the number of days a payment is delinquent. Is there a function I can use in a query field that would look at the numeric field and if the value of the subject field was:
< 60 Days "30-59 days"
< 90 Days "60-89 Days"
<120 Days "90-119 Days"
>=120 Days "120 Days +"

It was my understanding that the IIF function tests for a condition and can only return yes/no results. I seem to remember that SQL had something like a least or most function that would allow me to set maximum value for range of days. I apologize that my question is confusing and hope you can understand what I am trying to accomplish.
Thanks for any help

View 2 Replies View Related

Date Range By Month Number

Mar 7, 2006

I am trying to make a query that finds all records by the "Month Number" and have problems.

What I did:
Month(DateField)
I got month numbers like I wanted but when I typed in the criteria below, I got February thru December Records, but when I search on anything "up to" 9 it works fine.

I want:
Between{month1] and [month2]
Month1= 9 (example)
Month2= 12 (example)
Need results to be from the requested months but any year

I have to imagine it's how the date is converted to an integer, but not sure.

Any help would be great!

View 4 Replies View Related

Queries :: Number Of Students In Age Range

Sep 17, 2013

I have this formula for counting age groups

TOTAL 29 - 31: Count(IIf(DateDiff("yyyy";[DATE_OF_BIRTH];Date())>=29<=31;1))

But id does not work, if I use between 29 and 31 also do not work

I need to get count (number) of all students that have 29,30 and 31 year of age.

View 5 Replies View Related

Show Date Range Instead Of Week Number

May 1, 2008

When using the Format:'yyyy mm dd' in access queries.
When trying to show a week is there a way to show the
date range instead of week number.

Week 01 = Jan1-7. If the results are only week numbers
that makes it hard for people to understand when it
actually is. Bottom line is: I would like to convert
Week number to actual date range. Can anyone help with
this?

View 9 Replies View Related

MS Access Lookup And Compare A Range Of Number...is This Possible ?? Please Help !!!!

Jul 17, 2006

I was wondering can MS Access do a range compair look up. I have 2 tables. One is the Info table and the other is Rate table. The Info table has 3 columns like this:

[code]...

I want access to read the Info table and pick up the values in the FICO, LTV and Type and compair it. Like with the above example. In the Info table FICO = 622. So then I want it to carry the number 622 down into the Rate table and compare that the number 622 is in FromFICO and ToFICO column to find where does 622 falls in between. In this case there are nine rows in the Rate table that 622 is between 620 and 629. The next criteria is the LTV number in the Info table. It's 76. So back into the Rate table looking for the number 76. And 76 is happen to fall between 75.01 and 80.00 in the FromLTV column and ToLTV column. Next back to the Info table is the Type column which is 2. Now back to the Rate table to look up the Type column for Type = 2. So from FICO = 622 , LTV = 76 , Type = 2...So the rate I want to populate is 0.25...So is this possible to do in Access?

View 1 Replies View Related

Use Vb To Create New Records Based On Number Range

Apr 16, 2014

I am looking for a way to use vb, or any other way, to allow my users to enter a railcar initial and then a number sequence and add new records to the end of the table.

For example in a form the user can enter the car initial and the number sequence
Railcar initial: GATX
Number sequence : 290001 - 290100

I would like a way to create a temp table that then has 100 records
GATX 290001
GATX 290002
GATX 290003
etc etc.

I can then use this temp table in an append query to add them to my main table.

View 7 Replies View Related

Forms :: Limit Number Of Clients In Date Range

Aug 30, 2013

Ok, not sure if this is even possible or where to even start..

I've got a form that has all the info for a client, eg..
Client First Name
Client Last Name
Client Hours
Client WE/CS/EE
Client Day And Times
Client Phone
Client Comments

Begin Date & End Date (2 boxs) on there for date input..

and what i'm trying to do is limit that any date entered between begin date and end date, it'll check and make sure there is only ever 20 clients on any 1 day..

i thought Datediff would be the way to go, but then again how do you get it to search each day and make sure theres only 20 clients on there..

View 1 Replies View Related

Queries :: Assigning A Number Based On A Range Of Dates?

Mar 19, 2013

I have a table called StockTable with the following fields Location, Status, Serial, Make, Model, LastDate, DotNumber

I also have a table called FCDateRange with three fields

DateStart DateEnd and DotNumber

For example

3/7/13 - 3/13/13 - 1
3/14/13 - 3/20/13 - 2

Im trying to figure how to write a query that if the lastdate from the StockTable falls during the DateStart and DateEnd fields it will assign it the number in the DotNumber field

View 2 Replies View Related

Subtract Number Range While Keeping Track In A Table / Form

Mar 31, 2014

I have two tables (one is a query)

Table 1 (query based)

EMP_ID
Prev_Emp_ID
EMP_ID_DDSK

332-123
1
500

332-133
1
501

332-144
0

332-156
1
502

332-654
1
503

332-456
1
504

332-967
0

Table 2

Res_Numbers
Num_Of_Employees

500
10

505 - after 5 numbers are placed
Will be added for next hire

My goal: to place employee numbers into new employee field "EMP_ID_DDSK" (table 1), Numbers will come from (from "Res_Numbers" field (table 2)

Example: If field "Prev_Emp_ID" = false

Get the next number in line from table 2 "Res_Number" and place it in table 1 field "EMP_ID_DDSK".

Example: if "Prev_Emp_ID" = false for 5 new employees. Take the next set of numbers and place into field "EMP_ID_DDSK".

View 4 Replies View Related

Forms :: Automate Check Number Programmatically

Jan 16, 2015

I have a Paycheck form with a subform on it. The goal is to not have to click on the check field for every paycheck, instead use a Batch procedure. When you click in the subform (field) (CCheckNo) which gets the focus by default it advances to the next check number and performs other code operations. If you have a lot of checks this is not very user friendly. I created a command button on the subform to try and auto ate this process. Below is the code for this. It gives no errors but does nothing. The db is very complex. The form opens to the first record but can't be sure when the user could run the Batch procedure.

The idea was to try and use the OnClick procedure to run the event code. The field in question is CCheckNo which advances the check number.

frmBonusReimbursementChecks is the Main form.
(Check) form is a Subform
CCheckNo field gets focus on Load which is the Subform.

Code Below is on a Command Button on the Subform.

If MsgBox("Are you sure you want to Assign All Checks?", vbYesNo, "All Checks Confirmation") = vbYes Then
End If
If IsNull(Me.CCheckDate) Then 'If no Check Date, Assign One.
Me.CCheckDate = Date
End If
If IsNull(Me.[CCheckNo]) Then 'Advance to the next available Check Number.
Me.CCheckNo = Next_Custom_Counter

[code]....

View 2 Replies View Related

Modules & VBA :: Subscript Out Of Range If Run Twice

Sep 4, 2014

The below code exports a table (via a function) to a spreadsheet and saves it in a defined location. the code then opens the file does some work with it then moves it(left the move bit out as it works fine)

so when i run the code it works absolutle fine, table is exported, work is done and file is moved. however if i run the code again it fails, i get the error message out of range. i was originally getting this error when using the .usedrange.copy so i commented this out and now i get it on the next line that tries to work with the file so obviously something is wrong in my logic.

Code:
Private Sub Export2JDE_Click()
Application.Run ("JDE_Export") 'Exports to an xlsx file in the location described in the function.
Dim xlApp As excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

[Code] .....

View 8 Replies View Related

Modules & VBA :: How To Get Sum Of Range Of Data

Oct 4, 2013

I am trying to simply find the sum of a range of data...I have tried putting the range into a the formula, and also creating aliases for the cells but it either doesn't work or I keep getting a name error in excel and I just can't seem to get the right code:

The range I am trying to sum is:
Range(Range("B2"), Range("B2").End(xlDown)).Select

And I can find the cell that I want the sum to go into by:
Range("B2").End(xlDown).Offset(1, 0).Select

As I said I have tried a few things but just get this right. Should I be using Value, Formula or FormulaR1C1?

View 5 Replies View Related

Modules & VBA :: Sequence Number In Query Use A Starting Number

Sep 20, 2014

creating sequence number in a query using a starting number. For example i want to use the starting sequence number as 5

---SEQNUM---------COLOR-----------
-----5------------------blue--------------
-----6------------------red---------------
-----7------------------orange-----------
-----8------------------black-------------

View 2 Replies View Related

Modules & VBA :: How To Do A Join Range Query

Dec 9, 2013

I am currently trying to do a range join query in access (not an expert at all with it) but I realized that it's not easy. It probably needs a VBA code but still not sure how to do it. I basically have a column with number of days e.g

Number of days
18
1
0
56
8
-19
369

Eventually I would like to do a range join query (similar to excel range vlookup) with the following table

NumberofDaysWeeks
0 Same Day
1 1 Week
7 1 Week +
14 2 Week +
21 3 Week +
30 1 Month +
60 2 Month +
90 3 Month +
120 4 Month +
150 5 Month +
180 6 Month +
210 7 Month +
240 8 Month +
270 9 Month +
300 10 Month +
330 11 Month +
360 12 Month +

After joining the two tables with an approximate range (as in excel) the final table should look like below

Number of days Weeks
18 2 Week +
1 1 Week
0 Same Day
56 1 Month +
8 1 Week +
-19
369 12 Month +

Note that value -19 is an error so it should not return anything however i still want to see the value in the final table.

View 4 Replies View Related

Modules & VBA :: Count Of A Variable Range

Jul 30, 2015

1. I have a sample database table in excel where depending on column 2 value >1 is checked and logical results by IF check placed in Column D. The values having "Y" in column D are placed in Columns E:F.

2. Now I want to place count of values meeting the condition on last cell of the filled E:F range.

3. VBA code written by me proceeds fine and places values meeting condition in column E:F

4. I am beginner to VBA and finding difficulty in proper code for placing sum of counts of acceptable values in range F2:F7 in cell F8 ie last value in the array. Presently I have placed the value by In-built Count function in Excel.

File Count on Macro from another table is attached.

View 1 Replies View Related

Modules & VBA :: DLookup To Excel Range

Jul 17, 2014

I am having problems with Dlookup from a table which is linked to Excel.The heading on the fields from Excel have been imported as F1, F2, F3 etc... the code I am using is as follows .....

LOOK = DLookup("F6", "DATABASE_LOOKUP", "F1 = 1338087")

F6 = A date I am looking up.
DATABASE_LOOKUP = table I am looking up.
F1 = 1338087 = The record I am looking for.

View 5 Replies View Related

Modules & VBA :: Finding All Numbers In A Range?

Jul 16, 2014

I need to create a very simple database that would just store records and produce a couple of reports. I have three tables: one with the roster , one has records of the inventory items people from the roster receive and another one contains types of inventory we have. Everything is very simple except for one part. Every time we make a record of an inventory item given to someone, it requires not only employee id and inventory type from the existing tables. It needs us to enter a serial number of an item. This serial number contains a letter and a number.

Looks something like this - M100. Many people receive a consecutive set of inventory items. For example, from M100 to M150. There is no way to have a separate table with all serial numbers because they constantly change. That is why we need to have two text boxes that would allow us to input a range of serial numbers or just one number. Then the program should separate numbers from letters, evaluate the range, create new records of numbers and then put new numbers and a letter back together into one field in the table where we have all inventory records.

I found the following code online that allows me to find numbers within a range, but it only works for numbers.

Dim varRange As Variant
Dim lngLow As Long
Dim lngHigh As Long
Dim lngCounter As Long
DoCmd.Hourglass True

[code]...

I then found a piece of code that is supposed to separate numbers from letters, but I can't find a way to make it work.

Public Function FindNum(strName As String) As String
Dim strTemp As String
Dim i As Integer
For i = 1 To Len(strName)
strTemp = Mid(strName, i, 1)
If (Asc(strTemp) < 91 And Asc(strTemp) > 64) Or (Asc(strTemp) < 128 And Asc(strTemp) > 96) Then
FindNum = Right$(strName, Len(strName) - i)
End If
Next i
End Function

how to alter the code to make it work for my specific situation.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved