IF Code Breakdown ANyone?

Oct 24, 2006

Ok I know people are tired of code breakdowns but I got this code in the company's database and it is suppose to calculate 2 days before the mailing date. now to Text98 is not suppose to land on a Saturday or Sunday. The problem is in the code it is somehow ending up with the same date as the mailing date. I didn't write this and I have a module from the good people here that I use for my date calculation but I am not allowed to add to the company's database. Though I can only edit.

Can someone let me know why this code is pulling the same date as the mail date?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If WeekDay(Mail - 4) = 7 Then
Text98 = (Mail - 6)
End If

If WeekDay(Mail - 4) = 1 Then
Text98 = (Mail - 6)
End If

If WeekDay(Mail - 4) = 6 Then
Text98 = (Mail - 6)
End If

If WeekDay(Mail - 4) = 5 Then
Text98 = (Mail - 6)
End If

If WeekDay(Mail - 4) = 2 Or 3 Or 4 Then
Text98 = Mail
End If

End Sub

View Replies


ADVERTISEMENT

Breakdown Of Many To Many

Nov 6, 2004

I was wondering if somone could clear something up for me.

I understand that if there is a database with for example:

table_Cars(reg_num)
table_Customers(cust_num, cust_name)

there is a many to many relationship and so the inclution of

table_Order(hire_date, reg_num, cust_num)

would eliminate this.

I understand this but where I get confused is what would be done if a customer could hire out more than one car on the same order.

a Car can have MANY orders and and order can have MANY cars.

back to square one.

any guidence on this subject would be appriciated!

thanks

View 2 Replies View Related

Date Breakdown Problem

Nov 3, 2007

Hi,

I have the following situation:

I have two fields: Start Date and Stop Date and I need to create a procedure to break them down and create records.

Start Date Stop Date
01/25/07 03/05/07

Results:
01/25/07 01/31/07
02/01/07 02/28/07
03/01/07 03/05/07

Thank you in advance for any help.

View 2 Replies View Related

Queries :: Check If Period Breakdown Is Complete

Aug 31, 2014

I have 2 tables:
-tblServices
-tblPricesPeriods

Each of the tables has fields for:
-StartDate
-EndDate

The logic behind the database is that each service is contracted for certain period, from StartDate until EndDate (if EndDate is not given - null - it means it is a permanent contract). For each service, we need to input prices. As prices may change during time, we may have different prices records for a service - we need to have the service period broken down into subperiods with different prices. So we need StartDate and EndDate for each period for which particular prices are valid (again, if EndDate is not given - null - it means the prices are valid "forever" into the future from the StartDate).

My problem is: I want to have a check that would return the number of services which period (tblServices) is not fully reflected in the prices periods (tblPricesPeriods). Those would be for example:

-gaps between subperiods (subperiod1 finishing 30.09.2014 and subperiod 2 starting 3.10.2014)
-service period (tblServices) limited by EndDate (not null), while prices period for this service (tblPricesPeriods) having null as EndDate
-start date for service (tblServices) earlier than earliest start date for prices periods for this service (tblPricesPeriods)
-...

Preferably, I'd like to use a normal query for that (or set of queries), but I'm opened to other options.

View 7 Replies View Related

Queries :: Complex Query Sorting And Summing Amounts Breakdown

Jan 28, 2015

I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.

What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.

View 9 Replies View Related

Reports :: Display Breakdown Of Unique Reports In The Footer

Jun 16, 2014

I am trying to produce statistics reports in my database. The user selects the information they require in a form (date from, date to etc). A query then produces the results listing each job in turn which is what I want however at the end I would like to produce sub-totals for each client.

Depending on the criteria selected by the user, only some clients will appear in the list. Ideally I would like the footer to show something like:

Client 1 - 4 jobs
Client 2 - 1 job
Client 5 - 6 jobs
Client 6 - 17 jobs

I have tried using the field:

Code:
=Count([ClientDetailsName])

However this only gives the total number of jobs.

View 6 Replies View Related

Please Review This Code, (simple Code) New With Codes

Feb 16, 2006

Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.

Where did I go wrong?

Private Sub Form_Load()
Dim pw As Variant

If InputBox("What is the password?", "Password") = "1" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
If InputBox("What is the password?", "Password") = "2" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
End If
End If


End Sub

View 14 Replies View Related

Using Code To Unprotect And Protect Viewing Code

Jan 14, 2007

I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"

Is there a way to write code that will remove that Lock Project check and check it back on?

I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.

Thanks

SHADOW

View 6 Replies View Related

Code Help Please!

Apr 27, 2005

I have a button that runs a macro to insert NOW() into a text box.
This is how it is coded;

Private Sub Start_transferred_job_button_Click()
On Error GoTo Err_Start_transferred_job_button_Click

Dim stDocName As String

stDocName = "Start transfered job"
DoCmd.RunMacro stDocName

Exit_Start_transferred_job_button_Click:
Exit Sub

Err_Start_transferred_job_button_Click:
MsgBox Err.Description
Resume Exit_Start_transferred_job_button_Click

End Sub


How do I write the code to populate the [start Time] text box with NOW() when the button is clicked without using a macro???

View 1 Replies View Related

Need Help With Code

Nov 22, 2005

Need a little help here. I have this code on a command button that open a form that displays a chart. The chart work great as long as there is data to display. However, if there is no data then the chart is blank. So what I'm trying to do is add a dcount to catch the 0 and give an error. So the 1st query makes the table where the data for the chart come from. The Qry-Test for Zero query, queries that new table if dcount is 0 it should error.
My problem is that I can't get this to work with the 0 if I make it 1 then I will get the message box if there are no records and if there is 1 record 2 and greater work fine. So my question is why won't the code catch the 0?


Dim stDocName As String
stDocName = "Qry-Makes Table"
DoCmd.OpenQuery stDocName, acNormal ', acEdit

If DCount(" * ", "Qry-Test for Zero") = 0 Then
MsgBox " There is no data for this time frame to chart. Please re-enter your Date range"
Exit Sub
Else



Thanks

jon

View 1 Replies View Related

Need Help With Code

Nov 22, 2005

Need a little help here. I have this code on a command button that open a form that displays a chart. The chart work great as long as there is data to display. However, if there is no data then the chart is blank. So what I'm trying to do is add a dcount to catch the 0 and give an error. So the 1st query makes the table where the data for the chart come from. The Qry-Test for Zero query, queries that new table if dcount is 0 it should error.
My problem is that I can't get this to work with the 0 if I make it 1 then I will get the message box if there are no records and if there is 1 record 2 and greater work fine. So my question is why won't the code catch the 0?


Dim stDocName As String
stDocName = "Qry-Makes Table"
DoCmd.OpenQuery stDocName, acNormal ', acEdit

If DCount(" * ", "Qry-Test for Zero") = 0 Then
MsgBox " There is no data for this time frame to chart. Please re-enter your Date range"
Exit Sub
Else



Thanks

jon

View 1 Replies View Related

Code Help?

May 19, 2006

I use the code below on a search form. I would like for the results of the list box search to populate a report instead of the list box. Is it possible to take the the sql and move it to a report? Thanks..

Dim strsql As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strsql = "SELECT SiteIssues_tbl.IssueID, SiteIssues_tbl.SITE_ID, SiteIssues_tbl.IssueID, SiteIssues_tbl.AdminDate, SiteIssues_tbl.Occurring, SiteIssues_tbl.Issue, SiteIssues_tbl.Administrative, SiteIssues_tbl.Technical, SiteIssues_tbl.IssueComments, SiteIssues_tbl.CandidatesAffected, SiteIssues_tbl.RecordCreated, SiteIssues_tbl.User, SiteIssues_tbl.DateModified " & _
"FROM SiteIssues_tbl"


strWhere = "WHERE"

strOrder = "ORDER BY SiteIssues_tbl.AdminDate;"


'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.txtIssue) Then '<--If the textbox txtCenterName contains no data THEN do nothing
strWhere = strWhere & " (SiteIssues_tbl.Issue) Like '*" & Me.txtIssue & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtAdminDate) Then
strWhere = strWhere & " (SiteIssues_tbl.AdminDate) Like '*" & Me.txtAdminDate & "*' AND"
End If

If Not IsNull(Me.txtSite) Then
strWhere = strWhere & " (SiteIssues_tbl.SITE_ID) Like '*" & Me.txtSite & "*' AND"
End If

'Pass the SQL to the RowSource of the listbox

Me.lstSearchResult.RowSource = strsql & " " & strWhere & "" & strOrder

With Me.lstSearchResult
If .ListCount > 0 Then
GetListCount = .ListCount - 1 'Headings count
Else
GetListCount = 0 'Headings don't count if no items listed!!
End If
Me.Text31 = .ListCount - 1

Dim ctl As Control
End With
If Me.Text31 = -1 Then
Me.Text31.Value = 0
Me.lstSearchResult.RowSource = ""
DoCmd.RepaintObject acForm, "SitesIssues_qry"
Me.Requery


MsgBox "No Records Found."

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acCheckBox Then
ctl.Value = Null
DoCmd.RepaintObject acForm, "SitesIssues_qry"
Me.lstSearchResult.RowSource = ""
Me.Requery
End If
Next ctl
End If

View 1 Replies View Related

VBA Code

Jun 1, 2006

I have written the code below, which should work, however I am receiving an Error: Run Time Error '13' Type mistmatch on line:

Set cn = Application.CurrentProject.Connection.

Can anyone help?

Option Compare Database
Option Explicit

Private Sub cmdRun_Click()

Dim NICode As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Dim strSQL As String

Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
NICode = "txtLetter"

strSQL = "SELECT " & NICode & "1a, " & NICode & "1b, " & NICode & "1c, " & NICode & "1d & NICode & "1g & NICode & "1h FROM [WorkPlace NI Breakdown]"

rs.Open strSQL, cn

If Not (rs.EOF And rs.BOF) Then
MsgBox rs.Fields(NICode & "1a")
MsgBox rs.Fields(NICode & "1b")
MsgBox rs.Fields(NICode & "1c")
MsgBox rs.Fields(NICode & "1d")
MsgBox rs.Fields(NICode & "1g")
MsgBox rs.Fields(NICode & "1h")


End If
rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub

View 3 Replies View Related

A Code

Jan 3, 2007

Hi all,

I have code bellow which is bringing a tab called Heffalump from the excel spreadsheet into table. Now, what I need is to create a text box where I will be typing PI1228131313 and it's going to be changed in the code bellow.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test Import Specification", "K:FIDCPGCCGPI1228", True, "Heffalump!"

THANKS,
B

View 1 Replies View Related

A Code

Jan 3, 2007

Hi All,

I have a code bellow which takes data from Excel spreadsheet and puts it into table in access. I want to see this code in txtbox and be able to change the path's.. Please advice. I tried typing.. Me.mytextbox.value = but for some reason doesn't work..
Should I use Ufter Update or something like that..?


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Test Import Specification", "K:FIDCPGPI1206", True, "Heffalump!"

View 1 Replies View Related

Any Help With My Code;

May 3, 2007

Hi,

I 've converted the code to ADO method. It was working fine in DAO but know I get the following error and i can't figure it out the soloution.

Run-time error ‘3265’

Item cannot be found in the collection corresponding to the requested name or ordinal

Function ChangeQuantUnit(Material, quant_a, unit_a, unit_b)

Static M As New ADODB.Recordset
Static Mat_SQL As String

ChangeQuantUnit = 0

Mat_SQL = "SELECT SAP_Materials.Material, SAP_Materials.K, SAP_Materials.Base_K, SAP_Materials.KG, SAP_Materials.Base_KG, SAP_Materials.MTR, SAP_Materials.Base_MTR, SAP_Materials.ST, SAP_Materials.Base_ST FROM SAP_Materials WHERE (((SAP_Materials.Material)=""" + Material + """));"
M.Open (Mat_SQL), CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If unit_a = "LB" Then
ChangeQuantUnit = (quant_a / 2.2)
Else
If M(unit_a) * M("BASE_" + unit_b) > 0 Then 'this is the line that crashes
ChangeQuantUnit = quant_a * M(unit_b) * M("BASE_" + unit_a) / (M(unit_a) * M("BASE_" + unit_b))
End If
End If

M.Close

End Function

View 1 Replies View Related

Can Anyone Help With This Code Please

May 8, 2007

Can anyone help with this code please. I keep getting Error 13, type mismatch on the on click event of a Command button.

Private Sub Command6_Click ()
Dim tmpFilePath AS String
tmpFilePath = Me!Text1

DoCmd.DeleteObject acTable, "Expire"
DoCmd. TransferDatabase_ acImport,"MicrosoftAccess",acTable,"tmpFilePath","Expire","Expire",False

End Sub

What I am trying to do is delete a table and replace it with a table of the same name in another db. the path to the file is stored in Text1. The delete part works OK then the error message arrives, when I run it with a static file path it worked ok, but I want to pick up the path stored in Text1 on my form.:confused:

Thanks for your help

View 4 Replies View Related

To Code Or Not To Code.

Mar 21, 2007

In the database I am creating a table that houses “subjects’ names” and their “unique IDs”, which are both indexed with no duplicates. The Unique ID is used as a FK to connect all the tables that contain information from the subject. When subjects are enrolled in one of our studies they are assigned a “Unique ID”, which is assigned sequentially. The IDs are alphanumeric, and consists of 4 characters with the first character being a letter:

A001, A002…A999, B001, B002…C001 etc.:

To help avoid data entry errors, I would like to automate the process of assigning these Unique IDs as much as possible. So my question is:

Is it better to manually insert all the possible Unique IDs into this table upfront and then use a code that allows the user to assign the next Unique ID to the subjects they are enrolling (ie search the subject names column for the first null field and enter value there)? Or would it be better to set up a code that looks through the IDs that are already in the list and then automatically generates and assigns the next sequential ID to the person they have entered.

Also, where might I find some pre-established code to get me heading in the right direction?

Thanks in advance!:D :confused: :confused:

View 5 Replies View Related

Need Help In VB Code

Aug 22, 2006

Good day,

Iam traying to get result of my record as below

If next record is biger than previous show "1"
If next record is same previous show "1"
If next record is smallest show "0"
if next record is same previous show "0"

Example



idTimeCodePriceRes
110:00:00 AM1010905 0
46410:00:34 AM1010906 1
62610:00:56 AM10109061
152310:02:16 AM10109040
156810:02:21 AM10109040
156910:02:21 AM10109051



the code gave me correct result for some filed and other incorrect !

Could you please check the code and correct it for me.

I have attached the DB for any correction

Note: the command bottons on Form1 of Forms Object.

Iam very appreciated for assistant . :)

..

View 1 Replies View Related

Help With SQL Code

Dec 23, 2006

I need a littlle help adapting this piece of code to my purposes:

SELECT Clientes.[Codigo Cliente LAE], Clientes.Nome, Clientes.Endereço, Clientes.Cidade, Clientes.[Codigo Postal], Clientes.Telefone, Clientes.Fax, Clientes.[Numero Contribuinte]
FROM Clientes
GROUP BY Clientes.[Codigo Cliente LAE], Clientes.Nome, Clientes.Endereço, Clientes.Cidade, Clientes.[Codigo Postal], Clientes.Telefone, Clientes.Fax, Clientes.[Numero Contribuinte]
HAVING (((Clientes.[Codigo Cliente LAE]) Like "*" & Forms![Pesquisa de Clientes]!PCTextPesq2 & "*"));

I need to replace "Codigo Cliente LAE" by "Forms![Pesquisa de Clientes]!PCComboPesq" (I need to keep the [] of the the original code, just replace "Codigo Cliente LAE"). I've tried several ways but still get a sintax error.

Thanks in advance.

View 2 Replies View Related

Vb Code Help

Apr 21, 2005

Hi,

I am calling a stored procedure..through vb.SP expects 2 parameters.

but i m getting error "operation cannot be performed when the object is closed".This is the code

Dim db1 As ADODB.Connection
Dim cmd1 As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim rs1 As ADODB.Recordset

Set cmd1 = New Command
Set rs1 = New ADODB.Recordset
Set db1 = rtnConnection

Set cmd1.ActiveConnection = db1
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "spname"

Set prm1 = cmd1.CreateParameter("param1", adChar, adParamInput, 3, BMF)
Set prm2 = cmd1.CreateParameter("param2", adInteger, adParamInput, 4, chnnlstobook)


cmd1.Parameters.Append prm1
cmd1.Parameters.Append prm2

Set rs1 = cmd1.Execute

If Not rs1.EOF Then
txtfield= rs1(1)
End If

On rs1.eof it gives me that message

Pls any help on this i am trying a lot to find the solution for this.

View 3 Replies View Related

If Then Code

Aug 11, 2005

In the code below, I am trying to autofill a field called WOSD (Work Order Start Date). It works for the Door Types or just the -4 option.

However, if the SpecialColor checkbox is checked, it needs to subtract 6 days from the LotDelDate. I must have something typed wrong.

I have been trying to get it to skip weekends as well with no luck.

Can anyone give me a hand?

Private Sub LotDelDate_AfterUpdate()

If Me.DoorStyle = "Eagle" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-9" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "H/E" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "F/E" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-9" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-22" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Me.DoorStyle = "RP-23" Then
Me.WOSD = Me.LotDelDate - 5
Else
If Forms!FRMDELIVERY.Form.SpecialColor = "YES" Then
Me.WOSD = Me.LotDelDate - 6
Else
Me.WOSD = Me.LotDelDate - 4

End If
End If
End If
End If
End If
End If
End If
End If

End Sub

View 14 Replies View Related

What Does This Code Mean?

Oct 5, 2005

Hi, I'm analyzing a program made with MS Access. It has a whole bunch of tables and queries already made, and a form with lots of code behind it. I'm looking through and I'm not sure what the following means:

With CurrentDb
.QueryDefs("qry_cur_pier").SQL = _
" SELECT DISTINCT RE.AIRPORT_RESOURCE_NAME,
RE.AIRPORT_RESOURCE_KEY " & _
" FROM qry_cur_resource AS RE " & _
" WHERE RE.AIRPORT_RESOURCE_CODE = 'PIER';"

qry_cur_pier appears to be an already existing query. AIRPORT_RESOURCE_NAME and AIRPORT_RESOURCE_KEY are fields in that query. AIRPORT_RESOURCE_CODE is not in the query though. What does the RE. and the AS RE do here? Thanks in advance.

View 1 Replies View Related

VB Code Help

Sep 20, 2006

Hello,

Is there a way of changing VB code so that ONLYif a statement is true then the command runs.

This is what I currently have:

If Me.chkDoorChoice = True And Me.cboFrontDoorType = "N/A" Or Me.cboFrontDoorColour = "N/A" Or Me.cboRearDoorType = "N/A" Or Me.cboRearDoorColour = "N/A" Or Me.cboAdditionalDoorType = "N/A" Or Me.cboAdditionalDoorColour = "N/A" Then
DoCmd.RunMacro "mcrPleaseNote"
End If

So the command should only run when chkDoorChoice is true. There are occasions when cbofrontdoortype = "N/A" and chkDoorChoice = False and the macro still runs. I dont want this to happen.

Any ideas peeps?

View 5 Replies View Related

Not Sure How To Code This...

Nov 13, 2006

For example:

- the first request for the day would be "KEZ6313001AA" ...the 2nd would be "KEZ6313002AA" ...the 3rd "KEZ6313003AA"

- for the next day, it would be "KEZ6314001AA" ..."KEZ6314002AA" ...& so forth

- now the 'KEZ' is a constant string so it would always begin with "KEZ"; however, the rest is more complicated to concatenate.

- the next 3 numbers consist of the '6' from 2006 and the '313' is for the n-th day out of 365.

- the final alphanumeric number '001AA', '002AA' are the sequence numbers ('AA' will also stay the same); this number will reset to '001' each day.

I greatly would appreciate any assistance anyone could offer. Thank you!!!

v/r

aiikahn

View 5 Replies View Related

Code Help

Dec 13, 2004

Hola. Im new to this forum and new to Access. At the mo im busy with computing coursework and as such , am working with Access XP. I want to create Amount Paid and Amount Due fields in which the values automatically change as I input new data. Ie, total =£45. Amount paid changes to £5 and the due changes automatically. Can anybody help me? Thanks. Michael

View 1 Replies View Related







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