How To Send Count(*) --> Declared Integer

Mar 14, 2006

SQL Query
SELECT COUNT(*)
FROM Orders
WHERE (((Orders.Date) = [forms]![frmOrders]![DTPdicker2]))
VBA CODE

Option Compare Database
Dim iDay As Long
Dim iCount As Long

Private Sub DTPicker2_Change()
iDay = 0
iCount = 0
iDay = Weekday(Me.DTPicker2.Value)
iDay = iDay * 100
'iCount = Count(*) from SQL query above'
iDay = iDay + iCount
Me.txtRefNumber = iDay
End Sub


Basically I am trying to generate a meaningful reference number while adding new orders rather than using an ever growing unique id(AutoNumber), for creating Weekly Reports.

I have tried using the following code
iCount = DoCmd.RunSQL (qryReturnCount)
and I get an error

Any help would be greatly appreciated.
Last question for the gurus out their can it be done with the expression builder? To save me banging my head against VBA syntax.:mad:

Cheers Andy!!!

View Replies


ADVERTISEMENT

Modules & VBA :: Count Total Number Of Integer In A List

Oct 7, 2013

In one table, I have a few fields. One of the field is "ItemSequence" and another one is "TotalPcs"."ItemSequence" is where user key in the sequence number for one or more item. 5 example for possible content of "ItemSequence" is as following :

1) 7
2) 4,6,9
3) 5-9
4) 3,5,9, 23-25
5) 3-5, 8-10

"TotalPcs" is the total number of items key in to "ItemSequence". For the 5 example above, the related "TotalPcs" should be as following:

1) 1 (1 item, which is item 7 alone)
2) 3 (3 item which is item 4, 6 and 9)
3) 5 (5 item which is item 5, 6, 7, 8 and 9)
4) 6 (6 item which is item 3, 5, 9, 23, 24 and 25 )
5) 6 (6 item, which is item 3, 4, 5, 8, 9 and 10)

For time being, the user have to count manually to get the "TotalPcs". I wonder is there a way to calculate the "TotalPcs" by programming?

View 7 Replies View Related

Modules & VBA :: Enter Parameter Value When Already Declared

May 1, 2014

I'm trying to update a SQL table using a form button, and getting an "Enter Parameter Value" textbox prompt when the value is already declared. Not sure what is wrong. I know the value is valid as I'm returning it in a MsgBox.

Here is my code.

Code:
Private Sub cmd_data_merge_Click()
finish = [finish].Value
conv = DDEInitiate("EXCEL", "123 Staff List.xls")
For x = 4 To finish
cell_employee_number = "R" & x & "C9"
cell_surname = "R" & x & "C8"

[code].....

View 2 Replies View Related

Forms :: Cannot Use Calculated Value Declared As Public In Form

Jan 27, 2014

I wrote a program in Access 2003 and it works perfectly on my computer but when I run it in another one , there is a problem.

The problem is : on a form Ioad I calculate a value and I declared it as public. I need to use this value in my form but it does not recognize it. As if public statement does not work.

Is this from the Windows?

Again, it works on my computer but I have a problem elsewhere.

View 14 Replies View Related

Check If Integer

Oct 11, 2005

What the easiest way to turn text to an integer - ir have vaiable defined as long and an inputbox - want to keep asking for an input until I get an integer.

Have IsNumeric - can this be applied to int.

Or could I use localised error handling?

Thanks

View 9 Replies View Related

Year As Integer

Jul 13, 2005

There's a lot of info that I need to keep track of by just the year. If I enter it as a normal date, I would need to extract the year every time I need to query and then do what ever. Would it be easier just to extract it once, convert to an integer and use it like that through out the system when I need to query by year?

Thanks,

scratch

View 5 Replies View Related

Integer Length

Aug 30, 2006

I have a field, integer, that needs to be exactly 6 characters long, no more, no less. How would I set this?:p

View 1 Replies View Related

Long Integer Overflow

Aug 11, 2005

I have an autonumber field set up as long integer. The field just reached the value of 32670 and I get the overflow message. I thought a long integer
could be much bigger than that before running into that problem.

I got around it by re-creating the field and starting from 1, but would rather
know why it's doing it so I don't have users without their system.

Thanks in advance for any help.

View 4 Replies View Related

Changing An Integer To A Month

Oct 18, 2006

I have a form that asks what month you completed a file (04, is april ect.). From this form a report is opened, in my report, I have it read this number and I want to display the month for the number thats entered (if they enter 04 I want april displayed on the report). There is already a ton of data that has the MonthCompleted as 04, so changing that is out of the question.

Thanks, JOe K.

View 3 Replies View Related

Help - SQL Lookups (Text Or Integer)

Apr 27, 2006

Hi Guys,

First off, a big thanks to everyone on the site. I have learnt a lot since first discovering this site a few weeks back.

Problem:

Having understood that it is better to create SQL lookups to queries of tables rather than to the tables directly, I am having trouble understanding what value I should store in my main table, a text value or the ID (number) (of the text value.)

I have an asset table with a field Equipment Type. This field looks at a query of the EquipmentType Table.

Would it better to store the text value "Printer" in the main asset table (in which case I can query the table directly but the field will use up more space (i.e. 25 char)?)

or

Store the Equipment_Type_ID "1" relating to the Printer (will use up less space, but mean any queries querying the actual name would have to include the EquipmentType table).

Any advise would be much appreciated.

:confused: My thought was that I should go with the ID as otherwise I will be storing duplicate data. If this is the case, when would a text value be more suitable.

View 1 Replies View Related

Round Integer Division .5 To 1

Nov 8, 2007

Please help me with the round function. I want .5 to round to 1.
Here is an example of my data: (18+18+18+20)/4 = 18.5 rounds to 18. I want it to round to 19.

I used the following expression:
RoundACT Composite Score: Round((([Column1]+[Column2]+[Column3]+[Column4])/4),0)

Thank you.

View 6 Replies View Related

Convert String To Integer

Dec 4, 2007

Hi,

I have a field in my database which captures either single digit numbers or comments in text format. I want to be able to count the numbers but obviously I've had to use a memo field in order to capture both numbers and text.

The only way I can think of is to take the field and look for single character responses, then convert these into a number field so that it can be counted.

Does anyone have any idea how to do this?

Thanks.

View 3 Replies View Related

Using DCount With Long Integer?

Dec 5, 2011

I'm having problems Using Dcount function, when I use it with a text field like the following it works fine: (but using a Surname as a criteria can have problems.... I've people with the same surname in my database...)

times = DCount("[Surname]", "Booktoscore", "[Surname] = Forms!Teachers!Surname.value")

But, If I try to use it with a number, then it doesn't work, the problem seems to be with the criteria.... Because Access don't show me any msgbox with errors....

times = DCount("[IdCandidate]", "Booktoscore", "[IdCandidate] = Forms!Teachers!IdCandidate.Value")

View 5 Replies View Related

BeforeUpdate(Cancel As Integer) - Question

Aug 2, 2007

I'm trying to get the BeforeUpdate(Cancel As Integer) to work for me.

I have got it to work with Me.Date2 = Now()

But I do not want the Date and Time, just the date.

If I try Me.Date2 = Date

Nothing seems to work.

Any ideas, I do not know if there is another command that I can use or not.

View 10 Replies View Related

Using An Integer From A Subform To Filter A Report.

Aug 17, 2006

THis has to be an easy issue.

I have a subform that in the on curren event i passes the ClassId out to my main for in an unbound text box

Forms![Student]![ClassID] = Me![ClassID]

I know this part works

I then have a command button that should pass ClassID to a report so that it can be filtered. Here is the on_click code

Private Sub cmdReprintAccom_Click()
On Error GoTo Err_cmdReprintAccom_Click

Dim stDocName As String
Dim strReptCriteria As String

strReptCriteria = ClassID
stDocName = "Forms - Accomodations"
DoCmd.OpenReport stDocName, acViewPreview, , _
"[ClassID] = '" & strReptCriteria & "'"

My problem is that I keep getting a type mismatch error. I know that it is because CLassID is a number and it is getting passed as a string i just can't figure out the syntax to the highlighted code.

View 5 Replies View Related

Converting Integer To Time Format

Sep 5, 2007

Is there an easy way to convert an integer into time? What I want is for the person to enter in 0820 or even 820a and then convert it to 8:20 am. This is mainly to save keystrokes.

View 5 Replies View Related

Modules & VBA :: Changing From Integer To Text

Nov 23, 2014

I am having a query which having a category field like Electrical, Sports, House hold etc.

What I want that if i select Electrical then it should return 15, if Sports then 10 and so on i think this could be done through this below mentioned VBA but it need change from integer to text...

Option Compare Database

Public Function fncGrade(intNum%) As String
Select Case intNum
Case 0 To 1: fncGrade = "Same as Previous"
Case 2 To 32: fncGrade = "C-3"
Case 33 To 40: fncGrade = "C-2"
Case 41 To 50: fncGrade = "C-1"
Case 51 To 60: fncGrade = "B-3"
Case 61 To 70: fncGrade = "B-2"
Case 71 To 80: fncGrade = "B-1"
Case 81 To 90: fncGrade = "A-2"
Case 91 To 100: fncGrade = "A-1"
Case Else:: fncGrade = "X-X"
End Select
End Function

View 5 Replies View Related

How To Cast A String Into Integer Or A Real Number?

May 8, 2007

Iff(fico>600,1,0) as g,
I found fico is a string in access table. so the above does not work.
How to fix this problem
Thank you very much.

View 3 Replies View Related

Converting Two Date/time Fields To One Integer

Sep 15, 2006

Hi,

I'm combining two date/time fields in a query to an integer. The first field has the date, the second the time. I'd like the resulting integer to be without the opening 0. How can I do that?

That is, these are the two fields:
2006-09-14 (date/time)
15:00:20 (date/time)

And I'd like those two combined to be 60914150020 (integer) in the third field in the query.

Grateful for advice!

View 3 Replies View Related

Modules & VBA :: Return Integer Based On Time?

Oct 30, 2013

I have a query with a Start Time where the need to return a set integer in another field in my query. I am attempting to get this to work in my StripSecondsQry.

I am not getting any error messages and I am not getting any output, When I view this in the Locals window I can see that it should be returning 7, but instead I get nothing unless I change it to

Code:
Function SortStart(StartTime As String) As Integer

then I get zero.

I had this working within the query, but I had to add one more time and then received a message that the expression was too complex.

Code:
Sort_Start: IIf([StartTime]="7:00 AM",1,IIf([StartTime]="8:00 AM",2,IIf([StartTime]="8:45 AM",3,IIf([StartTime]="9:00 AM",4,IIf([StartTime]="9:15 AM",5,IIf([StartTime]="10:00 AM",6,IIf([StartTime]="10:15 AM",7,IIf([StartTime]="10:30 AM",8,IIf([StartTime]="12:00 PM",9,IIf([StartTime]="1:30 PM",10,IIf([StartTime]="1:45 PM",11,IIf([StartTime]="2:00 PM",12,IIf([StartTime]="3:00 PM",13,IIf([StartTime]="4:00 PM",14))))))))))))))

View 3 Replies View Related

Adding Decimal Place To Integer Field

Jun 9, 2015

Successful in loading an Access database with data from the PLC's memory. Unfortunately, I'm only able to transfer Integer values. In the database I'd like to show one of the fields as a real number by formatting the Integer value to add a decimal place. Example: 2505 to 250.5

View 11 Replies View Related

Forms :: Calculate Integer Difference Between Due And Result Date

Sep 20, 2014

Trying to calculate the integer difference between Due_Date and Result_Date excluding weekends and holidays.

I have a table (Holidays) with the dates of the holidays in it. The table looks like:

ID Description Holiday
1 New Years 1/1/2014
2 New Years 1/1/2015

So, if Date_Due: 9/25/2014 and Result_Date: 9/29/2014, then TAT = 2

Since 9/27/2014 and 9/28/2014 are weekends they are excluded from the calculation and only that Thursday and Friday are used in the calculation.

Code:
Private Sub Result_Date_AfterUpdate()
[TAT] = NETWORKDAYS(Due_Date, Result_Date, tblHolidays)
End Sub

View 7 Replies View Related

Modules & VBA :: SQL CAST Function - Return Integer Value Of String

Aug 28, 2013

Need to use CAST to return integer value of string (digits as data type string).

Where clause looks like this:

... Where Cast([Price File] as int) > 0

works fine in SQL Server but not sure what syntax is in VBA . Using Paul Baldy's suggestion to set Select statement as string and do the debug.print to verify that SQL has no goofs ... looks good but not to Access. What is proper syntax?

View 12 Replies View Related

Modules & VBA :: Calculate Integer Number Difference From Two Dates

Sep 20, 2014

Basically, I am trying to calculate a integer number difference from two dates (TAT = Due-Date - Result_Date). The number is calculated and excludes weekends and ideally holidays (for that I have a tblHoliday but not sure how to use it). The function below seems to calculate a number but doesn't exclude weekends.

For example, if Due_Date is 9/26/2014 and Result_Date is 9/30/2014, then TAT is calculated to be 5 (should be 2).Since 9/26/2014 is a Friday only Friday and Monday are used in the calculation.

Code:

Option Compare Database
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Long
Dim intCount As Long
intCount = 0

[code]...

View 1 Replies View Related

Converting Text Dates To Integer With Nulls Present?

Mar 18, 2013

I have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;

DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2))))
DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2))))
DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4))))
Time: IIf([TIME] Is Not Null,[TIME])

When I have a value of Null, i keep getting #Error, I think when it's null.

View 7 Replies View Related

Using Switch Statement In Access Query - Output Integer?

Mar 27, 2013

I am trying to use a switch statement in a access query and i be leave i have the code right but for some reason it will only output a string i need it to output an long integer . (All the columns that are in the statement are Numbers).

Code:
columnName:Switch (([column1]<>0), [column1], ([column2]<>0), [column2], ([column3]<>0), [column3],([column4]<>0),[column4],([column4]=0),0)

Outputs a string i need an long integer

I have tried

Code:
columnName:Switch (([column1]<>0), CLng(column1), ([column2]<>0), CLng(column2), ([column3]<>0), CLng(column3),([column4]<>0),CLng(column4),([column4]=0),0)

and does not work just outputs a string. It outputs the right number but not as an integer...

View 1 Replies View Related







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