The default numeric type is long integer, which is not capable of storing a 15-digit number, but there are types that are.
Change the data type to Number, then below in the field properties change Field Size to decimal and precision to how many digits you need (up to 28, in Access 2007.)
gershwyn
Last Activity: Jun 13 '11, 08:35 PM
Joined: Feb 10 '10
Location:
-
As Rabbit pointed out previously, it would help tremendously if you supplied sample data and the results you would expect to see from your query. You mention having seven counts, but other than digging through the code you provided, we have no details of what they should be or how they are calculated. If you can give us more details about what you want, I'm sure someone will be able to help you further.Leave a comment:
-
Where x , y , z , v are amounts of each guy in all other entities. ie: entities left, other than 1.
I've read this a couple times, and it's very hard to tell what you're asking. Are you saying that no matter how many entities there are, there should only be two columns: one for entity = 1, and a second for entity >= 2? On the off chance that is what you want, you can achieve it easily enough with your existing query. Just change...Leave a comment:
-
Access does not support the SQL you are trying to use here. The closest equivalent would be to use the Switch function.
Though personally, I'd prefer to create a table that cross references the code to the quantity, and use a join to lookup the values. That way any changes or additions could be made directly to the table without increasing the complexity...Code:SET quantity = Switch(Code = 8668, "2", Code = 8669, "3")
Leave a comment:
-
I'm assuming that the number is surrounded by spaces on either side. This function loops through, checking the characters between each pair of spaces in the string.
...Code:Public Function MaskDigits(StringValue As String) startPos = 0 Do While startPos < Len(StringValue) endPos = InStr(startPos + 1, StringValue, " ") If endPos = 0 Then endPos = Len(StringValue) + 1 If (endPos - startPosLeave a comment:
-
Code:DoCmd.RunSQL "INSERT INTO myTable (MyVariable) VALUES (" & myVar & ")"Leave a comment:
-
Wouldn't you want to sort in DESCENDING order, considering the more laps you've completed, the higher you should be ranked?Code:My problem now is that i cant do a ascending sort on this total in query.
Leave a comment:
-
Hi neelsfer,
I am not in a position to download your example. Please post the relevant details as text in your post. (It is also a good idea to post which version of Access you are using. I'm sure there are others who might take a look, but it's frustrating to download something only to find you can't open it anyway. I use 2007 but I know several experts on here are sticking with older versions.)
I still don't have much...Leave a comment:
-
There is nothing wrong with your syntax (though I would ditch the 'not' and reverse the 1 and 0.) You said you weren't successful with it - in what way? Are you getting an error, or is it not doing what you intended?Code:Sortlap1: IIf(Not IsNull([lap1time]),"1","0")
You didn't give any detail about how the data is stored in the table, but it's simple enough to sort that way given a normalized table structure....Leave a comment:
-
The loop is an excellent idea, but given that your controls all have similar names, with the only difference being the number at the end, I'm not sure it's worth it to store the values in an array. I think the simplest way to accomplish what you describe is like this:
...Code:Public Function ToggleButtons() For i = 1 To 6 CheckValue = Nz(Form.Controls("Value" & i).Value, 0) Form.Controls("cmdEdit"Leave a comment:
-
The AddNew method does not take any arguments, it just creates a new blank record for you to fill in. I'm not sure where you got the syntax you're using.
Try something like this:
Code:With AllocationsRecordSet .AddNew !Month = Month_Begin ![Service Category] = ServicesRecordSet!Service_Category !Minutes = Month_Minutes .Update End With
Leave a comment:
-
I think the simplest way to do that, given your existing code, is to create a table of holidays that your organization observes. We then just need to add a check to see if any given date is in that table.
I created a table called HolidayTable with two fields: HolidayName and HolidayDate, and filled it with a couple entries:
...Code:HolidayName HolidayDate New Year's 01/01/2011 Memorial Day 05/30/2011
Leave a comment:
-
You cannot include a field in your select list that isn't in your group by clause or part of an aggregate function. Your query is saying "show me the e-mail only once, but show me every date" - which can't be done.
If you're looking for just a list of e-mails, then drop the date from your select list. If you need the date included, you need to think about which date you want included. For example, you could use the Max() aggregate...Leave a comment:
-
You could do something like the following query (change "Table" to your actual table name, of course):
I'm guessing a little bit...Code:SELECT DCount(1,"Table","EmpID = " & [EmpID] & " AND Date <= #" & [Date] & "# AND Time <= #" & [Time] & "#") AS Row, [EmpID], [Date], [Time], [In/Out] FROM [Table] ORDER BY [EmpID], [Date], [Time]
Leave a comment:
-
Yes, it is smart enough to wrap around to the next year if needed. So if you specify a month of 12, it will calculate (Month + 1) = 13 and proceed with January, the same way it knows to treat a day of 0 as the previous day. It also handles leap years wonderfully, without having to code for the exceptions.
Test case:
...Code:?DaysInMonth(1, 11) 31 ?DaysInMonth(2, 11) 28 ?DaysInMonth(12,
Leave a comment:
-
This is a function I use all the time that will calculate the number of days in a given month:
The dateSerial part is a little tricky, but I'll explain. The last day of the current month is equivalent to the day before the first day of next month. DateSerial(Year , Month+1, 0) will give us that day,...Code:Public Function DaysInMonth(Month, Year) DaysInMonth = Day(DateSerial(Year, Month + 1, 0)) End Function
Leave a comment:
-
How to modify stored procedure to work on date range?
I have a procedure that populates the orders table with all orders with a delivery date in a seven day period, beginning with today. What I would like to do is be able to specify a beginning and ending date with input parameters, to override the default one week period. The dates in the table are stored as integers in YYYYMMDD format.
Here is my current (working) procedure:
...Code:CREATE PROCEDURE CVTORDERS LANGUAGE SQL MODIFIES
-
You should be able to match up each interval between updates using a self-join, doing something like below (assuming the timestamps are in a table called intervals.)
...Code:SELECT intervals1.userID, Max(intervals2.timestamp) AS StartTime, intervals1.timestamp AS StopTime FROM intervals AS intervals1 INNER JOIN intervals AS intervals2 ON intervals1.userID = intervals2.userID WHERE intervals2.timestamp
Leave a comment:
-
I can't say for sure what is causing those IDs not to show up, but I would start by rewriting the query. It is incredibly (and needlessly) complicated.
Is there any difference between the queries for each EID? If not, you can eliminate a lot of complexity by getting rid of the unions entirely, and writing one query that is restricted to the EIDs you want. You can use a where clause in the overall query to determine the list of EIDs...Leave a comment:
-
Maybe I'm misunderstandin g your question, but if the sold field can only be 0 or 1, can't you just sum it to get the total?
Code:SELECT MemberID, COUNT(KeyID) As TotalTickets, SUM(Sold) As TotalSold FROM Tickets GROUP BY MemberID ORDER BY MemberID
Leave a comment:
No activity results to display
Leave a comment: